Lookup Tables Design Question
Today Robert Asks, “How would you store categories from various tables?
Assume 50 tables with a category column, such as PurchaseOrderCategoryID, BuildingCategoryID, etc. There are about 20 categories per table, or about 1,000 distinct categories in the entire database.
Would you store each table’s categories in individual tables, such as PurchaseOrderCateogroy and BuildingCategory, or would you store them all in one table and use a table identifier column, such as the table name or entity ID?”
We talked about this question a few months back. Joe Celko (that name keeps coming up) wrote a blog which caught my attention about “One True Lookup Table”.
At the end of the day, this is generally considered a bad practice. The short list of answers why, in my opinion are:
- You can’t enforce declarative referential integrity using on true fact table…you have to use a trigger
- The thought is that having 50 tables with exactly the same schema complicates your database. I disagree…in reality you lose the clear communication in your Entity Relationship Diagrams because you have one table that is a pin cushion to almost every aspect of your database
- You end up with potential performance bottleneck (especially if there are many more entries than 1,000)
- It doesn’t simply code usage…a good object oriented developer will easily provide the necessary reusability of code because the structure of independent code tables is identical (code, value)
If you want to get into a lot more of the specific reasons, I highly recommend reading Joe’s blog. I don’t agree with everything Joe says, nor do I drink Celko Koolaid. In this case, I agree 100%.
Robert, if you want to save time, you can create a table to store the name of all these lookup tables…I do that in some databases. Then I can read from the metadata inside the INFORMATION_SCHEMA views and use that to generate ENUMs for code, and other interesting things as a code generator. I can even use that list to generate the SCHEMA for all my code tables because they all have similar column names and primary key constraints.
If you have a question regarding database design drop a note to btaylor@sswug.org. Let’s not dig into this question in detail since we covered it fairly recently…you can find the archives by going to editorials at www.sswug.org and clicking on the editorial link at the verry bottom of the page. Then, search for “One True Lookup Table” in the search box.
Cheers,
Ben
SSWUGtv
With Stephen Wynkoop
Keeping the end in mind: Laura Rose teaches you how to take stock of your business and make good goals.
Watch the Show
$$SWYNK$$
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Simplify encryption and key management on your SQL Server. Data thieves are targeting SMB companies because of their inadequa… (read more)
Featured Script
fn_TablifyString
Returns words from a text string in table form. Words defined as contiguous ASCII alpha chars. Returns words only; a modifie… (read more)