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

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 2

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 3

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