Look up tables helps greatly in queries, much similar to dimensions in data warehouse. However, if you implement it through SQL using CHECK constraints, it might not be the optimal solution. Remember, there are several ways to implement business constraints (from Application through codes). Look up table was not intended to minimize the load in DML but rather a mechanism in case you need to change business context or attributes of the BUSINESS OBJECT. In that way, you don't have to go through the all the databases (replicated) and tables just to add an additional attribute that is part of the primary entity. I think that this is the primary use of lookup tables-- scalability and manageability aside from old data integrity.

Try using data warehouse capability (with drag and drop) and see if you don't use somekind of lookup table. As a domain integrity, application codes can be the primary enforcer but having a constraint ensures that underlying data model and application are in sync (if in case someone passthrough the Application protection, i.e. through SQL Injection, it won't work because you have a safety net).

Actually, thinking further about the FK issues (CLT doesn't have an easy FK route, separate tables would of course) do application programs just use that as the error catch?

I mean: We have a test in the SAVE routine so that we can provide a nice friendly error message if an attempt is made to insert a row with a missing FK companion. We don't rely on the FK to intercept the error for us. Of course IF the FK is in place it a) saves us from ourselves and b) covers us for ad-hoc imports and the like.

But I wonder what people's opinions are on "how major is the absence of an FK is for CLT type codes?"

Actually, thinking further about the FK issues (CLT doesn't have an easy FK route, separate tables would of course) do application programs just use that as the error catch?...

Our application developers never consider the possibility of errors, so it is kind of the default.

I think that FK constraints are most valuable during development/testing to make sure the application code and stored procedures are at least getting data from the correct table.

I had an argument a few weeks ago with a developer who insisted there was something "wrong with the database" because it wouldn't let him insert data that violated a FK constraint. He insisted the procedure was correct. Of course, it wasn't.

DRI is a big thing to give up. Without it, you are just hoping that everything is properly tested. I think the codes table makes it even worse, where you hope your employees don’t end up with a job code of “Midnight Blue”, and a product doesn’t get a color of “Assistant Office Manager”.