Database Design and Reference Tables
-
This is not about critiquing the article (not much left to critique after everyone's shredded it to bits anyway) but defending the site owners as well as to laud rhunt's point - "...should not be censored....You have the power to discuss the idea...." as one that I fully concur with.
I once read an article on this site that completely misled me and when I wrote to Steve he said that it was not possible for him/them to scrutinise each and every article for anything other than the cursory/basic flaws of formatting/display etc. - and also that we're free to question the author as much as the authors are to defend/justify their arguments. Besides, anyone who reads articles usually does so because the "title" catches their interest (this includes the rookies) and no one stops with just reading them - they do go on to read the comments/responses - oftentimes these comments spin off into debatable points - but regardless of the direction that the responses take, the reader is usually not left in much doubt about the validity of any of the points addressed in the article.
Remember that not everyone knows everything there is to know about database design and development - one person's strong suit could be another's grey area - and there's really no way to tell if your idea is good or bad until you bounce it off the next person - one of the things that the site owners mention in their "write for us" section is - "you are subjecting yourself to critcism when you publish something" - I'm sure that everyone agrees that it's "much easier to be critical than to be correct"...so let's by all means be critical but preferably in an instructive rather than condemnatory way...
ps: ducking to escape the onslaught of any comments that my post may have unleashed..
- I'm on your side people..just want to see some kindness is all...
**ASCII stupid question, get a stupid ANSI !!!** -
Fair enough. Perhaps my call for a removal was a bit harshly worded.
I do recall seeing something before about "Steve (he) said that it was not possible for him/them to scrutinise each and every article for anything other than the cursory/basic flaws of formatting/display etc. "
I do think that articles listed in the newsletter should be reviewed for quality, don't you? I'm concerned that people de-value to newsletter/site by taking a (imho) poor article as representative of the content here - which (imho) is typically very good.
I guess I'm just worried about the people who don't read the opinions section (I would suggest that might be more than you might think - I know I don't always do it... :blush
Peace all...
-
My first reaction to this article was, Oh no not another Oracle DBA who was brought up on Forms.
The most common places I have seen this type of design is the "OLD" Oracle Forms method of attempting to improve development time by creating a generic lookup table with one form to support it.
While this idea may sound good in theory, in practice it really adds nothing to the overall cost and maintainability to a project.
How long does it take a new person to understand what is going on when you have a separate code table for each entity that your application supports, none. Now compare that to the time it would take a new employee the time to understand, I have to join to what? I have to use a category column with a string to perform a lookup? Huh?
I may be over-reacting but honestly, once you have your design done and implemented how many times do you actually add new code tables? Like one poster said, you end up adding new business rules and columns to existing code tables.
My favorite quote in the article is "Any kind “hardcoding” in stored procedures can be avoided by using this table."
So you don't consider that in every place you need to join to this table or use the "generic" retrieval stored procedure you will be "hardcoding" the category in every place. So you have just moved the hardcoding around, nice.
Enough of my rant now...
-
As soon as I read this article I wanted to reply with the long list of criticisims as to why the generic lookup table approach is a really bad idea. I see I am a little late to the game and there are a few posts prior to mine that seem to have addressed this already. It is a shame that this thread has digressed into the "free Speech I can post whatever I want/they can post whatever they want" rant.
Attempting to steer back on target: Generic lookup tables violate basic design principles on a few levels.
- Each table in the database should represent one (and only one) entity. Why would you mix ShippingTypes in with States?
- There is no "kind" of data in a database, just data. What I am getting at is how do you propose to define "reference data"? Data is data and each entity is represented by one table.
- You throw referential integrity right out the window with the generic design. You cant have foriegn key references that work with this design (and this is a good thing???)
- The author partially justifies this practice by the use of unintelligent numeric keys in his regular reference tables. this is using one bad practise to justify a worse one.
In my systems I make both developers and dba's happy by creating a generic procedure that loads data into standard controls. This procedure takes the tablename as a parameter and returns the data from that table. In this way, the dba has the correct database design and the front end developer has a generic procedure to call. In fact this procedure is written into our .NET base class that all pages are inherited from. Developers never even need to write this code.
-m
-
I have worked with Generic Lookup tables in the past. It was not my design, and did I voice my concern to management; of course I did.
The concept of one generic lookup table can be appreciated. But the execution and physical implementation will most likely be flawed.
All I have to say is, "Referential Integrity!!!!!!!" If you go this approach, anytime you have a FK to the lookup table, you would also need the category for each FK. This makes tables wider, indexes wider (composite) and overall more expensive for Query Performance.
I have to say that a generic lookup table is nice conceptually, but is truely a flaw for DB Design best practices. Sorry, but I wouldn't re-live the mistakes of working with this again. The first thing I would do is, "Normalize" for an OLTP system.
-G
-
Hi Guys,
We use this generic lookup table design. Our team has agreed that it is a good replacement for the reference tables with two columns.
We enforce RI using the check constraint with UDF's. This UDF is common, which takes two values, the key type (example CARD_TYPE) and value (VISA, MASTER etc), queries the lookup table returns a 1 or 0 if valid.
Also, to display the description to users, we created a common UDF that returns the description given the key type and value.
My opinion is, we cannot totally disregard this design..Think about the time saved for creating screens for each reference table. The downside is duplicates, which we avoided using controlled privilige to add records to these tables. We have only couple of users, who can add records to this table.
Baskar
-
Again, you are enforcing RI by code, instead of enforcing RI by true Referential Integrity. Yes, it has conceptual merit, but if you want to achieve quick performance on your writes and reads, is it worth the long-term risk.
But then again, if it works for you. Still, it is decidingly not best practice.
-
you said, "Think about the time saved for creating screens for each reference table."
Well you must have spent time in building your own RI and you must have spent time in building common UDF's. Again you have just traded one set of maintenance effort for a different set of maintenance effort.
And to address the screens, I have built applications that use one base code table screen object and each new code table screen is just inhierited from that, with different SQL statements in them. The core screen functionality is the same since it is all in a base class. It takes all of 15-30 minutes to produce a new code table in the application, fully tested.
I would rather spend a little effort up front to build a proper class library in an application then to try to build a common table in a database.
Just my $0.02
-
Think about the time saved for creating screens for each reference table. -- Baskar
Let me say this again: Code Generation. You shouldn't be creating these screens manually anyway. There were several other options for making the code more generic but you should never sacrifice data integrity fpr a miniscule savings in development time. Well, unless the data isn't important; if that's the case then maybe there are better projects to be working on.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Ahh, I didn't specifically address the old UDF enforced integrity in my article, but the same argument that I used against triggers applies here. Using the MUCK approach, your referencing table is not properly normalized. You have a Key_type column and a Value column in some table. I doesn't take much to realize that "value" is dependent on "Key_Type" and that "Key_type" can't very well be part of the key (despite its name).
No, it seems that we can't "totally disregard this design", despite the FACT that it stems from a severe lack of understanding of data management fundamentals.
What is amusing is that proponents of the MUCK approach provide the best arguments against its use, but you just don't know enough to understand it. Anyone with a solid grasp of the basics would immediately know that you have the "cart before the horse" by the following statment: "Think about the time saved for creating screens for each reference table." Does the term "Data Independence" mean ANYTHING to you? Who cares that you save a couple of hours by not having to create screens? That is such a minor component of any project that I've ever been involved in that the argument is a joke.
It is no coincidence that most of the posts here that imply (or state) actual experience maintaining or supporting systems using the MUCK approach are negative, while most of those supporting it focus on application development aspects. Those who would use MUCK tables are severely myopic.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I'm sorry, but the grade for this is an "F". Not using first normal form is a no-no, and this post should be removed.
-
Anyone that has every done any reporting or built a datawarehouse off databases like this can tell you to avoid this generic lookup table. Imagine having a fact table with 40 dimensions, how on earth do you do these joins??
Baaaaad baaad practice.
-
I think the poor author is totally embarrassed by all of this feedback and is now hiding under a rock somewhere…I feel a bit sorry for him but I agree with the majority of the posters here – MUCK’s are bad.
-
I've seen generic lookup tables work very well. In the situation I was in there was a view off of the generic lookup tables that returned the data that was in the (former) specific lookup table. I have never seen this layout before though. We left the generic lookup off of the database diagram and substituted the views where they were used.
-
Yup you end up having to make views for each of the distinct fields. So you double your work, then you build queries, you have queries that connect to views that have to execute queries. Do this with 20 or thirty lookup values once these tables get big and watch your performance dive bomb!
Viewing 15 posts - 31 through 45 (of 101 total)
You must be logged in to reply to this topic. Login to reply