SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is having a lookup table that is for multiple entities a common practice?


Is having a lookup table that is for multiple entities a common practice?

Author
Message
Rod
Rod
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6136 Visits: 730
I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed databases. Whenever we needed to have a lookup table for something, we would always create a lookup table for whatever. For example, let's say we wanted to have a lookup table for the states of the United States. We'd create a lookup table for that, with maybe a CHAR(2) for the key and then a VARCHAR(50) for the full name of the table. That's the idea I'm trying to convey.

At my current job my boss doesn't like doing that at all. Instead what he does is creates one, huge and wide (as in many columns) lookup table. Then he puts everything into it. For me, this is difficult because when I want to find something I've got to try and remember whatever special value in one or two columns it is that lets me know what other column has the value for the key (not the primarily key, but the designation that you'd use like 2 characters for a state abbreviation) and where the other column is for the full name, etc.

However, I'm wondering, perhaps its just me. In the past I've worked for smaller IT shops, with at most 5 people. Now I work for a large agency with over 200 IT people. Is this the way that larger organizations design their databases?

Rod
Andrew P
Andrew P
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3710 Visits: 2037
This is a controversial subject - the term for the structure is "One-True-Lookup-Table" (OTLT). It's a valid model, but I'm confident it's not the enterprise-grade best-practice it might look like.

This post from spaghettidba gives some good comments on the idea. I choose not to lock-in to this model (primarily due to it being non-standard, and difficult to add constraints to) and the majority of blog posts seem to weigh-in against the idea, but many people claim to have used it successfully. I can see benefits, mainly avoiding the schema growth that comes through many small lookup tables. I wouldn't mind it a new employer requested I implement a structure like this.

Andrew
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902163 Visits: 48702
IMO, it's an extremely bad practice.

It reduces database integrity, because foreign keys can't have filters on them, and so there's no way for the DB to prevent a country's lookup value been used in a StatusID or a Status's lookup code being used in the GenderID column (assuming there are foreign keys).
It encourages mistakes, like 'Active' being added to the lookup table with the ST (state) code, rather than the SS (Status). 'Active' in a lookup of states and provinces would be easy to spot, in a OTLT it's much harder to spot.

And on the design theory side, a table should have one thing in it. A table should have transactions or customers or vehicles, etc. When there's a table that contains cars, planes and watermelons it suggests something is wrong.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93105 Visits: 13622
Andrew P - Wednesday, February 8, 2017 11:59 PM
This is a controversial subject - the term for the structure is "One-True-Lookup-Table" (OTLT). It's a valid model, but I'm confident it's not the enterprise-grade best-practice it might look like.

I'd be inclined to describe it as an enterprise-grade worst practice, it's throwing away improved relational integrity provided by the relational model by introducing a table which does not conform to that model.


Tom

Sean Lange
Sean Lange
SSC Guru
SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)SSC Guru (254K reputation)

Group: General Forum Members
Points: 254928 Visits: 19495
I worked in a place that had one of these monstrosities. It was named MasterXRef. By the time I arrived it was as you describe, somewhat wide and way too length for a lookup table. It was on the order of 40-50 columns and thousands and thousands of rows. This was a fairly large system so the table had to be expanded multiple times. The column for the "LookupGroup" was a varchar and identity used as the primary clustered index. It was truly awful. We had a lot of orphaned data in there for systems that no longer existed, we had invalid group values because there was no integrity. And worst of all it was getting slower and slower to do something that developers need to do repeatedly (fill comboboxes with lookup values). It wasn't horribly noticeable for 1 or 2 comboboxes but each query would be in the 3-4 second range. Now consider a webpage with 15 comboboxes being loaded by 10 people at the same time. Suddenly a query that wasn't so bad is crippled. Suffice it say I would agree with the others here that this is a horrible design.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)

Group: General Forum Members
Points: 869993 Visits: 47405
Doctor Who 2 - Wednesday, February 8, 2017 9:57 PM
I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed databases. Whenever we needed to have a lookup table for something, we would always create a lookup table for whatever. For example, let's say we wanted to have a lookup table for the states of the United States. We'd create a lookup table for that, with maybe a CHAR(2) for the key and then a VARCHAR(50) for the full name of the table. That's the idea I'm trying to convey.

At my current job my boss doesn't like doing that at all. Instead what he does is creates one, huge and wide (as in many columns) lookup table. Then he puts everything into it. For me, this is difficult because when I want to find something I've got to try and remember whatever special value in one or two columns it is that lets me know what other column has the value for the key (not the primarily key, but the designation that you'd use like 2 characters for a state abbreviation) and where the other column is for the full name, etc.

However, I'm wondering, perhaps its just me. In the past I've worked for smaller IT shops, with at most 5 people. Now I work for a large agency with over 200 IT people. Is this the way that larger organizations design their databases?


I agree with the others. It's a really bad idea that most consider to be an absolute worst practice. The problem is that it's your boss doing it and he doesn't appear to be in the listen'n'learn mode on this one especially since it's his baby. The best you could do is to setup a test that proves that it's a huge mistake. Without such a test, he's not going to listen.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rod
Rod
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6136 Visits: 730
Jeff Moden - Thursday, February 9, 2017 9:41 PM
Doctor Who 2 - Wednesday, February 8, 2017 9:57 PM
I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed databases. Whenever we needed to have a lookup table for something, we would always create a lookup table for whatever. For example, let's say we wanted to have a lookup table for the states of the United States. We'd create a lookup table for that, with maybe a CHAR(2) for the key and then a VARCHAR(50) for the full name of the table. That's the idea I'm trying to convey.

At my current job my boss doesn't like doing that at all. Instead what he does is creates one, huge and wide (as in many columns) lookup table. Then he puts everything into it. For me, this is difficult because when I want to find something I've got to try and remember whatever special value in one or two columns it is that lets me know what other column has the value for the key (not the primarily key, but the designation that you'd use like 2 characters for a state abbreviation) and where the other column is for the full name, etc.

However, I'm wondering, perhaps its just me. In the past I've worked for smaller IT shops, with at most 5 people. Now I work for a large agency with over 200 IT people. Is this the way that larger organizations design their databases?


I agree with the others. It's a really bad idea that most consider to be an absolute worst practice. The problem is that it's your boss doing it and he doesn't appear to be in the listen'n'learn mode on this one especially since it's his baby. The best you could do is to setup a test that proves that it's a huge mistake. Without such a test, he's not going to listen.


Jeff, this is a very interesting idea. And I'm beginning to think one I'd better to something about, soon. I've been working with this data design architecture for over a year, but it's taken me a very long time to completely comprehend what my boss wants this general purpose table to do. Just this week I've realized that it is not only the One Master Lookup Table (or One True Lookup Table as spaghettidba puts it), but my boss also wants it to be used for saving user preferences for where windows are to be placed, sort order preferences, etc. Everything related to how the user interacts with the application we're writing. Also all reports information is to be stored there, such as the customized reports a user wants and the definition of those reports. I wouldn't be surprised at all if I haven't yet comprehended everything this table is meant to store. But let me ask you a question about what you've suggested. Right now we're been working on some applications (3 currently) which are not yet in production. So up to this point there's been no contention for reading or writing to the table. How can I simulate load onto this table, such as might happen in production?

Rod
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)SSC Guru (264K reputation)

Group: General Forum Members
Points: 264217 Visits: 12273
Doctor Who 2 - Saturday, February 11, 2017 6:54 PM
Jeff Moden - Thursday, February 9, 2017 9:41 PM
Doctor Who 2 - Wednesday, February 8, 2017 9:57 PM
I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed databases. Whenever we needed to have a lookup table for something, we would always create a lookup table for whatever. For example, let's say we wanted to have a lookup table for the states of the United States. We'd create a lookup table for that, with maybe a CHAR(2) for the key and then a VARCHAR(50) for the full name of the table. That's the idea I'm trying to convey.

At my current job my boss doesn't like doing that at all. Instead what he does is creates one, huge and wide (as in many columns) lookup table. Then he puts everything into it. For me, this is difficult because when I want to find something I've got to try and remember whatever special value in one or two columns it is that lets me know what other column has the value for the key (not the primarily key, but the designation that you'd use like 2 characters for a state abbreviation) and where the other column is for the full name, etc.

However, I'm wondering, perhaps its just me. In the past I've worked for smaller IT shops, with at most 5 people. Now I work for a large agency with over 200 IT people. Is this the way that larger organizations design their databases?


I agree with the others. It's a really bad idea that most consider to be an absolute worst practice. The problem is that it's your boss doing it and he doesn't appear to be in the listen'n'learn mode on this one especially since it's his baby. The best you could do is to setup a test that proves that it's a huge mistake. Without such a test, he's not going to listen.


Jeff, this is a very interesting idea. And I'm beginning to think one I'd better to something about, soon. I've been working with this data design architecture for over a year, but it's taken me a very long time to completely comprehend what my boss wants this general purpose table to do. Just this week I've realized that it is not only the One Master Lookup Table (or One True Lookup Table as spaghettidba puts it), but my boss also wants it to be used for saving user preferences for where windows are to be placed, sort order preferences, etc. Everything related to how the user interacts with the application we're writing. Also all reports information is to be stored there, such as the customized reports a user wants and the definition of those reports. I wouldn't be surprised at all if I haven't yet comprehended everything this table is meant to store. But let me ask you a question about what you've suggested. Right now we're been working on some applications (3 currently) which are not yet in production. So up to this point there's been no contention for reading or writing to the table. How can I simulate load onto this table, such as might happen in production?

First off, let me agree with the others. Storing multiple lists of unrelated data in different columns of a single table is just asking for trouble. It's a disaster waiting to happen. Like Gail pointed out, if you want to inactivate one status, does that mean the state, region, district and division on the same row are all inactive because the Active bit was set to zero?

I can't assume to know exactly what Jeff is thinking, but I had an idea that might be along the same lines. If I'm "seeing the table" you describe properly, this might drive the point home. Let's say you have 30 different lists with 1 code and 1 description each for a total of 60 columns in your table. You also have 10 user-setting columns in there, storing the saved values. From the sounds of it, you'll likely have an XML or large varchar column or two.

Your application has to display the contents of the 14th list in a dropdown, so write the query to return that list. Take a look at the number of reads it took to build that list. Now that that's done, it should always work, right? Well, populate 1 or 2 of the user columns and make sure to run the table out to 1 million rows. The size of the 14th list hasn't changed, so the query to return the data should still be the same, so run it again. How have the reads changed? Now run the table out to 5 million rows, repeat the query for the 14th list and take a look at the reads. If you had multiple users trying to query the 14th list to display in a dropdown at the same time, what do you think the consequences of the design would be?

Granted, the problems you encounter could be addressed with nonclustered indexes, but the problem with the physical design is still there. Make no mistake - the physical design is important and it's difficult to change once it's in production. I don't know how many users are going to be using your applications or how many rows your tables are going to contain, but you should really design your application to scale up front or you'll face a significant amount of pain when you have to fix it later in life.

Like I said earlier, I'm not 100% sure if I'm "seeing the table" you describe accurately. The DDL for the table would help.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)SSC Guru (869K reputation)

Group: General Forum Members
Points: 869993 Visits: 47405
Doctor Who 2 - Saturday, February 11, 2017 6:54 PM
Jeff Moden - Thursday, February 9, 2017 9:41 PM
Doctor Who 2 - Wednesday, February 8, 2017 9:57 PM
I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed databases. Whenever we needed to have a lookup table for something, we would always create a lookup table for whatever. For example, let's say we wanted to have a lookup table for the states of the United States. We'd create a lookup table for that, with maybe a CHAR(2) for the key and then a VARCHAR(50) for the full name of the table. That's the idea I'm trying to convey.

At my current job my boss doesn't like doing that at all. Instead what he does is creates one, huge and wide (as in many columns) lookup table. Then he puts everything into it. For me, this is difficult because when I want to find something I've got to try and remember whatever special value in one or two columns it is that lets me know what other column has the value for the key (not the primarily key, but the designation that you'd use like 2 characters for a state abbreviation) and where the other column is for the full name, etc.

However, I'm wondering, perhaps its just me. In the past I've worked for smaller IT shops, with at most 5 people. Now I work for a large agency with over 200 IT people. Is this the way that larger organizations design their databases?


I agree with the others. It's a really bad idea that most consider to be an absolute worst practice. The problem is that it's your boss doing it and he doesn't appear to be in the listen'n'learn mode on this one especially since it's his baby. The best you could do is to setup a test that proves that it's a huge mistake. Without such a test, he's not going to listen.


Jeff, this is a very interesting idea. And I'm beginning to think one I'd better to something about, soon. I've been working with this data design architecture for over a year, but it's taken me a very long time to completely comprehend what my boss wants this general purpose table to do. Just this week I've realized that it is not only the One Master Lookup Table (or One True Lookup Table as spaghettidba puts it), but my boss also wants it to be used for saving user preferences for where windows are to be placed, sort order preferences, etc. Everything related to how the user interacts with the application we're writing. Also all reports information is to be stored there, such as the customized reports a user wants and the definition of those reports. I wouldn't be surprised at all if I haven't yet comprehended everything this table is meant to store. But let me ask you a question about what you've suggested. Right now we're been working on some applications (3 currently) which are not yet in production. So up to this point there's been no contention for reading or writing to the table. How can I simulate load onto this table, such as might happen in production?


The trouble with "load testing" is the you must not only load test his solution, you must also load test YOUR solution under the same simulated load. Have you designed a system to replace his? If not, the battle will be lost. Remember that most bosses don't want to be presented with problems... they quite rightly want to be presented with solutions. It's a rare thing to run into a boss that won't listen if you present at least a Proof-of-Principle that clearly demonstrates a reasonable advantage in the areas of ease of use/maintainability, extensibility, performance, and reduction in resource usage.

If you can't do something like that, then you'll have to learn to enjoy the ride your boss has created and, perhaps, help him improve the current form of the system.. Heh... at least it's not some bloody form of XML or JSON! Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902163 Visits: 48702
Doctor Who 2 - Saturday, February 11, 2017 6:54 PM
How can I simulate load onto this table, such as might happen in production?

Gut feel, load will not be the first problem you run into. First will probably be data integrity problems caused by bugs in the apps or people changing stuff in the DB (or things like Excel connected directly to the DB), and that's not really something you can simulate


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search