Database Design - Hard Coded String Identifier

Question

What does everyone take for renaming a binding code to a row identifier in a database table? I am really looking for a cleaner alternative. What if, for example, your static rows in this table are identifiers 1,2,3, and then this table is populated with user transactional data using ID 4-100, and then you later want to add a new row identifier, which in your local production the database is the identifier of row 4, but when that row gets into the customer database, it should be 101 ... well, something seems to break everything.

So, how do you handle static locked rows in a table that is also populated with transactional data?

Thanks, MeshMan

Answer 1

Do not do this.; -)

If you have static rows, values ​​that never change, in a table that has user data that is transactional or at least mutable, then I would say that you have at least a normalization problem in the schema.

Reference data usually belongs to its own table. If the table itself contains only reference data, then assigning identifiers from the application or using the generated identifiers from the database becomes a matter of preference.

I often played with the idea of ​​generating either an Enum source code class from DB tables, or populating DB tables with Enum class information at the time of build / deployment, but I never “bypassed”.

Answer 2

i agree with Ken G - the presence of enumeration values ​​corresponds to row identifiers; it makes sense only for search tables with static (unchanged) content

Answer 3

Do not base the special logic in your application on the identifiers of the rows in your database, especially if you do not have absolute control over what will happen in this table. (I admit that sometimes I do this for lookup tables that I absolutely know will not change, but even here it is probably bad practice.)

If you need to mark certain special entries, then put some kind of “flag” field that indicates this, and a request for this flag.

Answer 4

I agree with Stephen W. that a good solution is to use the name string of the ENUM element. I use this method all the time in .Net and it is easy to use.

The idea is that no matter what value your enumeration has (and no matter what identifier the database records have), the name of the enumeration will only change if your developers change it. Compared to an automatically increasing identification number (out of your control), this is a much more manageable situation. In addition, other developers reading your code know that the name is trying to describe, unlike some seemingly random number, which can mean anything, and probably can mean several things in different databases!

One way this might work is to specify a column of text code or description. It may be an alpha-numeric value, but the key is that it must uniquely identify this entry. In fact, this can be used as a primary key, but in practice I always have an auto-increment number of the primary identification number. Example columns on such a table:

PK_ID | CODE | Other Data PK_ID | CODE | Other Data etc.

Here, the value of the code field is actually your name Enum. This is a code that does not need to be changed. You must establish a rule between yourself and your team that they DO NOT CHANGE, but if someone needs to change it, make sure that this is reflected on both platforms.

Using .NET to work with Enum (called SortDirection in this example):

 ' Get the string name of an enum [Enum].GetName(GetType(SortDirection), SortDirection.Ascending) ' Get the enum value from its string name CType([Enum].Parse(GetType(SortDirection), "Ascending"), SortDirection) 

This is not an ideal solution, but it served me well; I have been confused and annoyed by the hard-coded identifier too many times so as not to appreciate the best of these two evils. Hope this helps!

Answer 5

You may be able to post an example that will help us better understand, but if you have several rows designated as “system” records, you might consider adding a column to the table called “Order”, and then when you create the record , designed to log in, you can increase the Order column.

For most other things, such as lookup tables, you need to more clearly manage and use row identifiers.

Answer 6

Well, an enumeration should be a constant - something you don't expect to change - that you really can't say about the data from the database.

In any case, the problem is that you save / load INTEGER behind the ENUM text. A solution that would help here would be to look for lookups / saves / etc from the TEXT value of the enumeration

This VB code translates the string back to ENUM, and it's easy enough to wrap it in C #:

 [Enum].Parse(System.Type, Value) 

Answer 7

Here's the solution: most databases have a mechanism for generating new identifier values ​​that start at 1 and increase in the positive direction.

But if the primary key column is a signed integer, you can use negative values ​​for your static rows. Identifier -1 means "--NONE--", identifier "-2" means "SPECIAL" or something else. If you need more static lines, move in the negative direction.

Then your foreign keys can refer to both static and user strings if the foreign key is also a signed integer.

Answer 8

ROWIDs should never be stored, because they can change. If you move one of these tables to a new tablespace, all your saved ROWIDs will become invalid (in any case, this is true in Oracle).

If you have a special line for FK purposes that needs to be protected, you can use a trigger to prevent it from updating.

Answer 9

Do not judge the rationality of your plan. DBA and other people always like to say: don't do it. "As if you asked if you want to do this or not, and even if you did not ask about it, as if you had a choice not to do it.

I assume that you asked your question for some reason and that you have no choice not to do this.

I have a friend who thinks that Oracle sequences are a pain and that autonumber fields are much simpler. They are not so "light", but they are much more flexible. At this point, you have not indicated your platform, so there is no mod-1.

In Oracle, you create sequences to populate autonumber fields. Sequences are completely table independent. You can use one sequence in several tables, you can use several sequences in one table. If I had to solve your situation, I would create 2 sequences that begin with 1, and steps 2 (for administrator data) and one starting with 2 and step 2 (for user data). I would modify my insertion procedure to have a parameter parameter for admin insertions in order to output the correct sequence. you will never encounter your data and the data of your users. There is a side effect of being able to distinguish between two based on the parity of the identifier. The disadvantage is that there is a hard limit.

Answer 10

I should have done this earlier in the status table. There were certain “built-in” values ​​that should always exist when an application is installed and processed specifically at specific points in time.

Then came the opportunity for users to create their own workflows.

What you can do is start your sequence with 1.001. Any values ​​from 1 to 1000 are "built-in" values. Of course, 1000 is a magic number here, but in my case I only had 5 built-in values, so it seemed pretty safe.

Another option is identifiers that exceed zero created by the user, and less than zero are built into the system.