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! 😉