| Sloan's profileGranada Coder, C#, Sql S...PhotosBlogLists |  Tools  Help |
|
5/6/2009 One True Lookup Table DiscussionI recently saw an article on the "One True Lookup Table".
Code Sample Here (Right-click, Save As works best)
The author makes 2 points.
//
In most cases, neither of the above applies, and responsibility for data integrity resides solely in the application code.
Show me such a database, and I’ll show you some data where the code does not match anything in the lookup table.
//
Well, I can provide a solution that uses a UDF (user defined function) and a check constraint.
And the statement "and I’ll show you some data where the code does not match anything in the lookup table" would not be valid if this solution is employed.
The FK exists to keep integrity.<<(This exists in addtion to the constraint)
And the check constraint makes sure that you only can apply values of the correct "CodeCategory".
(Aka, I cannot put in a OrderStatus value into a column wanting a Country or similar).
The solution is Sql Server specific, but the idea is there.
Keep in mind I use this for ~most tables. I still create a few standalone lookup tables when the cookie cutter does not work.
I am not saying this is the best solution in every scenario. There is a performance issue with validating the check constraint for every INSERT/UPDATE action.
But for bulk data inserts (or updates), you can drop the constraint and readd it if that becomes an issue.
TrackbacksThe trackback URL for this entry is: http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!569.trak Weblogs that reference this entry
|
|
|
