I come across the situation where there are many lookup tables with simple look up table structure in general. In database design I am anticipating each table with type and status looup along with some more lookups. In total if I have 250 data tables, it will take 500 lookup tables which will have 1500 DAL stored procedures and 1500 ins/upd/del triggers. I came across a way to simplfy this architecture by putting code and codeoption tables using combined lookup into one lookup table. I can write custom constraints to enforce database integrity.
I want some expert advice. Do you see any issue with this kind of approach I used this earlier and worked great for me but with the new era of code generation, is this make sense to have common lookup table or have 500 lookup tables
Any view with pros and cons will be appritiated.
Thanks
Lookup table architecture
BufW
GIJoehosaphat
Like most architectural questions, the answer is "it depends". Basically your are trading off maintainability and simplicity against clarity and accuracy. Obviously if the status and type values are the same for every table then it's a no brainer to go with a common lookup table but I assume they're at least somewhat different so you have to examine the tradeoffs. If each tables has it's own set of lookups, it's always clear what values go with what table and a consistent naming convention can make it obvious to developers and DBA's who follow you which lookups go with which data tables. Also you eliminate the need for a footnote in every design document and data model that explains how to figure out which lookup values correspond to which data table. You also eliminate the little box with 250 lines to it that represents the lookup table in any data models you generate.
On the other hand, a single set of stored procedures and a single lookup table it much easier to maintain. A single change to the way a lookup table works is much easier to do to one SP then 250. The down side of you solution is that if you decide to repartition your database in the future because of changing application requirements or need for scaling out the application, you will have to figure out how to split the single table into multiple aggregate lookup tables. Anyone adding another data table, another lookup value or another type of lookup values will need to understand how the lookup aggregate works and probably stands a good chance of breaking something else.
There are probably also organizational issues to consider. Are all the data tables accessing this common lookup table owned by the same developer and same DBA If so, who owns the lookup tables If this isn't an issue now, is it likely to become one in the future as the organization and application evolves.
Bottom line, if it was my decision, I would go with separate lookup tables and work out techniques for dealing with the extra stored procedures and triggers. Maybe a program that generates all the lookup table related procedures and triggers so there's a single place to make changes or a single set of parameterized procedures.
Sorry for the long winded response but I think there are a number of things that you have to consider.
slothmo
DanteTheCoolOne
This makes sense to me to have combined approach rather than 500 lookup tables. As you rightly said, you can set some custom constraints and at DAL level, create some fuzzy class(es) which will return you appropriate lookup only.