Database Design - Hard Coded Row ID

Question

Hi Everyone,

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

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

Answer 2

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 3

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

Answer 4

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 5

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 6

Wow, thanks for the speedy responses guys :).

Ok, let me elaborate.

Database rules are all columns are not allowed to be null and MUST have a default value (usually this is just 0, or '', or false to satisfy the data type).

The table "Employee" can reference a "Department", but "Department" table has a static row in there named "-None-".

[Employee] - ID, Name, FK_Department [Department] ID, Name

Employee's dont have to be assigned to a department but must have a valid value in the foreign key to the department table. So the department contains a row in there with the name "-None-". This is simply to satisy the foreign key constraint.

Now, there are other "system" type departments that always exist in the dpeartment tables too like application defaults such as "Finance, Marketting, Production" etc. However, the user can create new custom departments which also go along side that data in the departments table.

We currently have a column on the department table called "Locked" and is set to true if this value is "Locked". This row ID is recorded in a tabled called "LockedRows" which contains TableName, RowID, and RowName. So say the user wants to create an employee without a department, we have a helper class that does GetLockedRowID("TableName", "RowName") and it gives back the ID...

• Absolute nightmare! When it comes to replication....oh the pain......

So would you guys say it would be more correct to move out the static departments in to a seperate table and keep user generated data in another table? Would imagine that would be difficult to enforce as a foreign key in the Employee table...

Answer 7

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 8

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 9

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 10

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.