| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-08-27 : 13:07:43
|
| We have about 100 different lookup lists - for things like drop-downs and options. Everytime we need to add another list, it's the same old story: - Copy and Paste the table structure of another code table, - Copy and Paste the Insert, Update and Fetch Stored Procedures from another Code Table, - Copy and Paste the Maintenance Form in the Application from another Code Table - Copy and Paste the VB Code for the Maintenance Form from another Code TableTo make matters worse, we forgot to include an Active ('yes'/'no') column in these 100+ tables for setting old items to Inactive.To make matters even worse we are changing our front end meaning a re-write of the 100+ forms.Anyone have thoughts about going to a Universal Lookup table? Personal experiences?Some of the criticism I've heard:- slower - big concern- Referential Integrity - can add an additional column for the Code Table and use the CodeTable, Code ID jointly as a PK/FK- No such on 'one-size fits all' Universal Table - we could use it 'just' 95% of the time then |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-27 : 13:12:58
|
| I've seen them used and they always cause problems. Unless the owner or some C-level forces me, I'd never use one. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-08-27 : 14:27:54
|
| Referential integrity is very hard to achieve.Performance is usually much worse.Programming against the model is much harder and more than makes up for the little bit of time you save setting up new tables.It’s a violation of first normal form, so it doesn’t really fit with that whole relational database paradigm.In general, it’s a stupid idea.CODO ERGO SUM |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 14:56:43
|
| Referential Integrity would not be hard:CodeTable---------PK: CodeID, CodeTypeOrders-----------...OrderStatusCodeIDOrderStatusCodeType - default to 'OrderStatus'FK: (OrderStatusCodeID, OrderStatusCodeType) -> (CodeID, CodeType)Voilates 1nf? How? Is 1nf violated if I combine nuts and bolts in a Part Table?In terms of time saved, in addition to the database Development time saved shouldn't we also consider the total time - include Application Develpment time saved as well. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-27 : 15:01:42
|
| it's a violation because you have to copy code type to the child tables too.it also prevents you from making the description field unique -- which it should be.it multiplies the # of reads your query performs by roughly the number of tables you'll combineit mixes apples and oranges. why would you store, for example, vehicleType, orderType, and customerType in the same table?also, it leads to bugs in client code. there is a long list of reasons that you shouldn't do it |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-08-27 : 15:22:25
|
| OK, so you are going to add an extra column and check constraint on every referencing table, and you think that is going to make development faster? Leaving aside the extra space it is going to use in the database.Yes, it is a violation of first normal form. Nuts and bolts are both parts and a single type of entity. How exactly do zip codes, color codes, store codes, manufacturer codes, and vendor codes seem to be even remotely the same type of entity?Databases should be designed to acurately model the data, not to suit the needs of developers who don't feel like doing the work. In any case, it will not save you any development time.CODO ERGO SUM |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 15:31:14
|
quote:
Originally posted by russell
it's a violation because you have to copy code type to the child tables too.
that's not a violation of 1nfsame as any other composite PKquote:
it also prevents you from making the description field unique -- which it should be.
no, it doesn't: unique index (CodeType, Description)quote:
it multiplies the # of reads your query performs by roughly the number of tables you'll combine
are you sure?quote:
it mixes apples and oranges.
excellent point: a FruitTable with column FruitType: apples, oranges, ...quote:
why would you store, for example, vehicleType, orderType, and customerType in the same table?also, it leads to bugs in client code. there is a long list of reasons that you shouldn't do it
|
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 15:37:34
|
quote:
Originally posted by Michael Valentine Jones
Yes, it is a violation of first normal form. Nuts and bolts are both parts and a single type of entity. How exactly do zip codes, color codes, store codes, manufacturer codes, and vendor codes seem to be even remotely the same type of entity?CODO ERGO SUM
If all they consist of is a Code, Description and Status. Then they are of a single type of entity: List Option. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-27 : 15:49:37
|
| As others have pointed out - repeatedly, it is not a good idea. But, if you want to simplify your coding and maintenance, you don't need to duplicate all of the code over and over again.You could easily create a generic set of utilities to update/manage your lookup tables. Once that is done, add a new lookup table - update your dictionary of lookup tables, and your utilities should be able to manage that new lookup table. As long as the structure of the lookup tables is the same - you wouldn't have any problems with this.Even if your lookup tables have additional data elements - which is very possible, you can still create generic utilities to manage them.What you should not do is create a generic table - that is definitely going to cause you grief later on.Jeff |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-08-27 : 15:50:47
|
quote:
Originally posted by denis_the_thief
quote:
Originally posted by Michael Valentine Jones
Yes, it is a violation of first normal form. Nuts and bolts are both parts and a single type of entity. How exactly do zip codes, color codes, store codes, manufacturer codes, and vendor codes seem to be even remotely the same type of entity?CODO ERGO SUM
If all they consist of is a Code, Description and Status. Then they are of a single type of entity: List Option.
Since that is not all they consist of, then they are not a single entity. You forgot the CodeType column, which would be better named the EntityType.It sounds like you have made up your mind to ignore the advice of everyone who has responded, so good luck with the project.And good luck with the re-write down the road to fix the problems it causes.CODO ERGO SUM |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 15:55:50
|
quote:
Since that is not all they consist of, then they are not a single entity.
In that case fine, you're right. But we literally have 100+ tables where that is all they consist of, that is our situation. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 16:01:03
|
quote:
It sounds like you have made up your mind to ignore the advice of everyone who has responded, so good luck with the project.
I haven't ignored any of it at all. Nor have I we made up our mind yet either.I haven't call any of it 'stupid' either. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-08-27 : 16:40:32
|
quote:
Originally posted by denis_the_thief
quote:
It sounds like you have made up your mind to ignore the advice of everyone who has responded, so good luck with the project.
I haven't ignored any of it at all. Nor have I we made up our mind yet either.I haven't call any of it 'stupid' either.
You would feel comfortable calling it "stupid" if you had seen the damage that a "one lookup table" can cause and had to go through the effort of having to fix a live system that was implemented with one.CODO ERGO SUM |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 16:55:54
|
quote:
You would feel comfortable calling it "stupid" if you had seen the damage that a "one lookup table" can cause and had to go through the effort of having to fix a live system that was implemented with one.
Could you at least describe a little what was there to fix? So far I'm going to tell our team something like: many people are saying it's a bad idea. But I'll have difficulty pin-pointing why. Aside from the performance hit, although if there is a Clustered Index on (CodeType, CodeID), I'm wondering if that hit may not be so large. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-27 : 17:02:16
|
quote:
Originally posted by tkizer
How many rows are we talking about in this universal lookup table?Tara Kizer
approx. 100,000. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-27 : 17:26:21
|
| Yikes! Performance would be a concern, especially if the table is queried a lot. Even if your universal lookup table takes say 100ms to return, the old design would be much faster. Whatever the additional time to query the universal lookup table adds up quickly. Now I'm not saying that 100k rows is a lot of data in SQL Server, but it is a ton of lookup data that has an easy solution (your current solution).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-27 : 17:38:18
|
quote:
Originally posted by denis_the_thief
Could you at least describe a little what was there to fix? So far I'm going to tell our team something like: many people are saying it's a bad idea. But I'll have difficulty pin-pointing why. Aside from the performance hit, although if there is a Clustered Index on (CodeType, CodeID), I'm wondering if that hit may not be so large.
Yes it's a bad idea. But, will it work? Sure, it'll work just fine. What if you needed to create 100 data bases on a single server? Normal logic would dictate that you would, probably, create 100 databases on the default instance. But, there are some people out there that think it's a better practice to create 100 INSTANCES of SQL on that server and put one database into each instance. Boggles my mind, but more power to them.In a similar vein, creating the master loop-up to rule them all is a bad idea. Just tell your peers to read or watch The Lord of the Rings...  EDIT: Damn hit the wrong button for I was done typing..As a contrived example, what if you receive a requirement that one or two or any amount of "Types" need to have a ordinal field so the UI can sort it properly. Not too big of a deal. Add the ordinal column and you are on your way. Granted all the Types that don't need the ordinal are using up disk space. Then the requirement changes a little, that ordinal field also has to be unique per type group. Now you need to constrain the Type, Value and Ordinal. But, you have to keep all the other Types that don't "have" an ordinal unique. Maybe the logic the inserts into or updates the table doesn't use a stored proc (?) then the logic needs to be updated all over the place to make sure that an insert/update takes care of the ordinal value. Etc.. Etc.. Maybe that's not a valid scenario. But, you can see that any change to business rules for one Type then has to be supported by all Types. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-27 : 17:56:44
|
| Also, I might have skipped past this, but it makes writing constraints very difficult. For example, ISO Country Codes are 2 characters. If you want to constrain that and other values of different lengths by type, the constrain on that columns can get pretty hairy. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-27 : 18:22:41
|
| Denis, I'm not sure why you're making condescending remarks or getting upset.You asked a question. A few very knowledgable people gave you their opinions based on a combined many years of experience.Of course you're free to do as you see fit.One day, someone will have to come along and fix it if you blow it now.Keep in mind - THIS is your chance to build it right. Those chances don't come along very often. Usually we have to deal with the garbage some ill-informed "architect" left behind.Often times, we make guesses as to the solution when someone posts a question, based on the information they provide. In this case, it's not a guess. It is a bad idea. For all of the reasons we've posted, despite your objections.Most of your comments in reply have quite frankly been wrong.I don't mean to come off like a jerk. And certainly don't have hard feelings. Good luck with the project and keep posting if you have more questions or need more detailed explanations. |
|
|
|
Next Page
|
Universial Lookup Table vs. Separate Tables