Database Design - Hard Coded Row ID

Question

What's everyone take on tieing code enum's to row ID's in a database table? I'm really looking for a cleaner alternative. What if for example your static rows in a given table are ID's 1,2,3, and then that table gets filled with user transactional data using ID's 4-100, and then you later want to add a new row ID which in your local production database is row ID 4, but when that row goes to a customers database it will have to be 101...well that kinda breaks everything.

So how do you handle static locked rows in a table that also gets filled with transactional data?

Thanks, MeshMan

Answer 1

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

The idea is that regardless of what value your enum's have (and regardless of what ID's the database records have) the enum name will only change if your developers change it. Compared to an automatically incrementing ID number (out of your control) this is a much more manageable situation. Also other developers reading your code know what a name is trying to describe, as opposed to some apparently random number which could mean anything, and could very likely mean multiple things across different databases!

One way this could work, is for your table to have a text code or description identity column. This could be an alpha-numeric value, but the key is that it should uniquely identify that record. In reality this could be used as a primary key, but in practice I always have an auto-incrementing primary ID number column anyway. Example of columns on such a table:

PK_ID | CODE | Other Data etc.

Here, the value of the code field is actually your Enum name. This is a code that should not need to change. You must lay down the rule amongst yourself and your team that these DON'T CHANGE, but if somebody needs to change it then make sure it is reflected on both platforms.

Using .Net to work with an Enum (in this example one called SortDirection):

' 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)

Its not a perfect solution but it has served me well; I have been confused and irritated by hard coded ID's too many times to not appreciate the better of these two evils. Hope that helps!

Answer 2

I have had to do this before on a status table. There were certain "in-built" values that must always exist when the application is installed and are treated specially at certain points in time.

Then, there was the ability for users to create their own for workflow reasons.

What you could do is start your sequence at 1,001. Any values between 1 and 1,000 are "inbuilt" values. Of course, 1000 is a magic number here, but in my case I only had 5 inbuilt values so it seemed pretty safe.

The other option would be ids greater than zero are user created and less than zero are system inbuilt.

Answer 3

Not judging the rationality of your plan. DBA's and other people always like to say, " don't do that." as if you asked if you should do it or not, and even if you didn't ask that, like you have a choice to not do it.

I will assume that you asked your question for a reason and that you don't have a choice to not do this.

I have a friend who thinks that Oracle sequences are a pain and that autonumber fields are so much easier. They aren't as "easy" but they are much more flexible. To this point you haven't stated your platform, so no mod -1 please.

In Oracle you create sequences to fill in autonumber fields. Sequences are completely independent from tables. You can use one sequence in multiple tables, you can use multiple sequences in one table. If I had to solve your situation, I'd create 2 sequences one that starts at 1 and steps by 2 (for admin data) and one that starts at 2 and steps by 2 (for user data). I'd change my insert procedure to have an option parameter for admin inserts to pull the correct sequence. you'll never have collisions of your data and your users data. There's a side effect of being able to distinguish between the two based on the parity of the ID. The downside is that there's a hard limit.

Answer 4

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

If you have a special row for FK purposes that needs to be protected you can use a trigger to prevent it from being updated.

Answer 5

Here's a solution: most databases have a mechanism to generate new ID values that start at 1 and increment in the positive direction.

But if the primary key column is a signed integer, you can use negative values for your static rows. ID of -1 means '--NONE--', ID of -2 means '--SPECIAL--' or whatever. If you need more static rows, advance in the negative direction.

Then your foreign keys can still reference both static and user-generated rows, if the foreign key is also a signed integer.

Answer 6

Don't base special logic in your application on Row IDs in your database, especially if you don't have absolute control of what will be in that table. (I admit I sometimes do this for lookup tables that I absolutely know will not be changing, but even here it is probably bad practice.)

If you need to flag certain special records, then put some kind of "flag" field that indicates that, and query on that flag instead.

Answer 7

Well, an enum should be a constant--something you're not expecting to change--which you really can't say about data from the DB.

Anyways, you're problem comes from the fact that you're saving/loading from the INTEGER behind the text of the ENUM. A solution which will help here, would be to do lookups/saves/etc from the TEXT value of the enum

This VB code translates a string back to an ENUM, and it should be easy enough to port to C#:

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

Answer 8

i agree with Ken G - having enum values correspond to row IDs only makes sense for lookup tables with static (unchanging) content

Answer 9

Maybe you could post an example to help us better understand, but if you have some rows that are designated as "system" entries, you could consider adding a column to the table called Order and then, when you create an entry that is designed to be the system entry, you can increment the Order column.

For most other things, like lookup tables, you should be able to control the row IDs more explicitly and use them.

Answer 10

Don't do that. ;-)

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'd say you have at least a normalization issue in the schema.

Reference data belongs in it's own table, usually. If the table itself contains only the reference data, then assigning the IDs from the application or using generated IDs from the DB becomes a matter of preference.

I've often toyed with the idea of generating either a 'source code' Enum class from DB tables or filling DB tables with Enum class information at build/deployment time, but I've never 'gotten around to it'.