Database Design and Reference Tables
-
I just flagged this article as a Dud before I read everyones comments in the forum. I agree this one should have never been published.
-
If you're worried about building an application to maintain a bunch of tables you shouldn't be. Just use a code generator to build the application. You don't have to go all "object oriented" for basic data maintenance. You can build a good set of stored procedures and generic application components to achieve the generic nature of the data while still maintaining good database design.
It's not difficult to build the maintenance UI but it is boring, tedious, and repetitive. Those are the things that should scream code generation in your head. The generic table solution may sound neat at first but it would be a nightmare for someone new coming in to maintain the application.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Bit of a worry that the author has 8 years' experience in database design! I had this exact argument with a new (front-end) developer on my current project who maintained it had been done the "generic" way at every previous place he'd worked. Oh dear.
-
I flagged the article as a dud and I can't believe it was posted on this site. Definitely not a recommended practice. We had a programmer here who did that (along with several other horrors of programming and database design) and there is no way I would ever do that.
-
Yes, agreed. This generic lookup table is not the best idea. Nonetheless, I really don't believe that calling the quality of this site into question, or questioning the editing prowess of Steve Jones, for that matter, is in order. Hasn't this always been a site to communicate ideas - both good and bad?
My point is, I'm not sure the article, or should I say the IDEA, should be censored by the site. You have the power to discuss the idea and RATE it! The current 2 star rating will probably be an indicator to junior developers/dba's that this not a commonly accepted practice.
RH.
-
It is funny that author wrote: 'This article is about smart database design'
The initial memo should say:
'This article is about poor database design that may justified from time to time'.
-
Well I've seen the uglier side of look up tables as well.
I've seen databases with hundreds of lookup tables, views, stored procedures enough to make your skin crawl. When the development and dba staff has a large print out from one of those plotters and you see mustard/ketchup stains from the numerous lunches/dinners eaten over the thing trying to digest it ( pun intended ), you realize sometimes the better way is a combination of schemes likes this and lookup tables.
-
I too worked with a product that used generic lookup tables. It actually spread the lookups between multiple tables so you had to join up three tables to get to the value. It was a huge pain and very difficult to maintain since it wasn't clear at first glance how the tables joined up. I would agree with the others posting here that it's really not a good design. There's no harm in creating a bunch of specific lookup tables.
Of course, generic lookup tables are better than what my current company uses: NOTHING! We just have those infamous magic values in our databases. And no primary keys. And no foreign keys. And they're case sensitive. *weeping*
Speaking to the quality of the article, I have to agree with a few others that it should never have made it onto sqlservercentral. While I appreciate the site and I've found a few helpful articles in the past, I think there are far too many examples of these articles that sound great at first until you realize that they're only about four paragraphs of fluff. They're either completely wrong or they're talking about something so obvious that you feel dirty after having read them.
-
rhunt,
with respect, i don't think this is the same as "the worst practices" series that was kicking around a while ago. that was good.
it seems to me that this article has been submitted in all seriousness as A Good Idea and i just don't get that.
I've been visiting SQLServerCentral and swync before that for years (off and on) so i know how much high quality stuff there is here. i was v. shocked to see such a bad idea listed on the site and, potentially, given much more kudos and a far bigger audience than it deserved.
you say that junior developers/dba's will spot that this is not commonly accepted practice but that's not the point. i come here to see good ideas and get good quality information and i've always got that. i'm just disapointed, that's all.
-
I inherited a datawarehouse which used two generic tables:
CREATE TABLE [typeGrp] (
[typeGrpId] [int] IDENTITY (1, 1) NOT NULL ,
[typeGrpDesc] [varchar] (40) NOT NULL ,
PRIMARY KEY CLUSTERED ([typeGrpId]),
UNIQUE NONCLUSTERED ([typeGrpDesc]))
END
and
CREATE TABLE [type] (
[typeId] [int] IDENTITY (1, 1) NOT NULL ,
[typeDesc] [varchar] (40) NOT NULL ,
[typeGrpId] [int] NOT NULL ,
[typeAltText] [varchar] (50) NULL ,
[sortOrder] [int] NULL ,
PRIMARY KEY CLUSTERED ([typeId]),
UNIQUE NONCLUSTERED
([typeDesc],[typeGrpId]),
CONSTRAINT [FK_type_typeGrpId] FOREIGN KEY
([typeGrpId]) REFERENCES [typeGrp] ([typeGrpId])
)
END
TypeGrp lists the category (e.g. Job Code). TypeAltText on the type table is used for cases where users wanted an abbreviation of some kind.
I created a simple user function to pull the text description as
select dbo.fnTypeDesc(ForeignFieldName). For drop-down web forms there's a stored procedure which takes the typeGrp as a parameter and returns the typeId and the typeDesc.
There's a FK constraint on TypeId on the tables that use it.
From a practical point of view, this handles about 99% of the cases. For the others with unusual criteria I create specific lookup tables. Everyone seems to be blasting generic tables, but this has worked very well in our situation.
-
I have to agree with the majority of the poster's here that GenericLookup tables are usually bad idea. I have used them on a rare occasion to fix a particular problem (the why escapes me now). But Ranga's solution takes the concept of GenericLookup table to a whole new level (worse).
By having the strCategory column, you are limiting the lookup table to be used in one column in one table! What if, in his example, you want to have a state lookup for Employee addresses? Another set of 52 entries for Employee.State? Can you imagine trying to setup separate lookup tables that can only be used for one field? Ugh!
I agree with EM about the headaches trying to analyze a DB diagram with hundreds of lookup tables. My solution is to have two diagrams, one with "major" tables that give a view of the DB structure "at 10,000 ft", the second complete view with all lookup tables included.
Finally, I disagree with those that want the article removed from the site, or fault the SSC editors with allowing the article. This site is for the exchange of ideas. And, like free speech in our country (USA), we should allow alternative opinions that provoke thought and exchanges like this. This will do more to educate all DBA's, junior and senior, than having a narrow focus. And sometimes (though not necessarily in this case) it will challenge us to rethink our ways, to keep us fresh. So I vote no for any prior- and post-censorship.
Mark -
Darren, my take on it is that Steven was commenting on the article, not your post. In any case, I agree with the cacophony of opinions expressing condemnation of this single table approach to lookup tables. Yes, it is easier, but only in the short term. As another person posting pointed out, you quickly run into situations where one of the lookup entities suddenly needs another column of some sort. I've seen this happen time and time again. Normalisation is there to make things easier in the long term and in general practice. I would suggest that the article's author learn and use it.
JBB
-
Relax, it's not about censorship or free speech. It's about the value of a 500 word article that's not really bringing up any point that's particularly earth-shattering, not to mention hotly contested by about 95% of the respondents.
By your logic, I could submit an article entitled "Databases Are Cool" and write a couple hundred words of junk and have it posted here. And if it wasn't posted, I could claim that I was being repressed and censored. It's about quality and what we expect on a site with an editor.
Again, I'll stress that I'm not trying to be harsh. This is a free site and the guy who runs it probably puts a lot of time into it. I appreciate that time and the site. I just wish some of the articles would be screened a little better.
-
Now you can make that 97% as this is also wrong in my opinion... but I like the discussion that ensued
. -
Finally, I disagree with those that want the article removed from the site, or fault the SSC editors with allowing the article. This site is for the exchange of ideas. And, like free speech in our country (USA), we should allow alternative opinions that provoke thought and exchanges like this. This will do more to educate all DBA's, junior and senior, than having a narrow focus. And sometimes (though not necessarily in this case) it will challenge us to rethink our ways, to keep us fresh. So I vote no for any prior- and post-censorship.
I totally agree that the site is about free exchange of ideas. I would be very happy to have seen this article as a entry in the forums, with the revelant discussion thread that has taken place being represntative of people sharing their ideas (and giving the poster chance to (a) learn, or (b) post a rebuttal/reasoning <as you refer in your comment regards challanging to rethink>
However, my specific concern is that by listing this as an article, it is give a level of creditability that I (and pretty much most other posters) agree it doesn't warrant. Then to have it as the "featured article" for the NewsLetter just increases that.
Of course, as always, these are my opinions - feel free to disagree
Viewing 15 posts - 16 through 30 (of 101 total)
You must be logged in to reply to this topic. Login to reply