January 9, 2009 at 4:56 am
Hi All,
Please give me suggestions on storage structure of pick list (dropdowns)..
I have following options, please post your view on this.
1) Create seperate table for all pick lists.
2) Create single table for all pick list with flag to differentiate them.
I have used both of them in different projects, but not sure about what is the general practice about it and which gives better performance.
Right now I am working on project, which have 100+ pick lists (approx total 500 records initially and later can increase. Means avg. 5 records per list.). This project is hosted solution so 500 records is for one client and there can be thousands of cliets (currectly approx 1000 clients).
Project platform is
-.Net 3.5
-C# for desktop
-ASP.Net for web
-C# for Middle tier
-Sql 2008 Or Sybase for backend. We have to support both SQL and Sybase.
Please post your view on this.
Regards,
Nitin
January 9, 2009 at 6:06 am
I've done both and I have to say, having 100 seperate pick lists is easier, more functional, easier to maintain, easier to document and easier for others to understand, support and document.
There was an article posted here on SSC that called the other design, if I recall correctly, Massive Unified Directory (or something) but the acroynym was, of course, MUD.
I don't hold this as a good reason to use the 100 seperate tables, but, it's worth mentioning, it's actually more accurately in compliance with the rules of normalization. All hail Codd, the great and powerful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2009 at 6:35 am
Hi Grant,
I have tried to search for article (MUD), you have mentioned but not finding naything. Can u pls post some details, so that I can find the article? And also post the links, if you have any, on this topic.
Regards,
Nitin
January 9, 2009 at 6:52 am
See, my memory stinks. It's Massively Unified Code Keys (MUCK) tables.
The first article:
http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/[/url]
Here's a great discussion about the topic too. One True Lookup Table is another name for the design:
http://www.sqlservercentral.com/Forums/Topic154165-204-1.aspx
Suffice to say, it's done, but it's not terribly popular and you will regret it down the line. Where I work now, we have one system that has it, but fortunately, after years of pain, we're rewriting that system in SQL Server 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2009 at 7:35 am
Grant Fritchey (1/9/2009)
See, my memory stinks. It's Massively Unified Code Keys (MUCK) tables....
Suffice to say, it's done, but it's not terribly popular and you will regret it down the line. Where I work now, we have one system that has it, but fortunately, after years of pain, we're rewriting that system in SQL Server 2008.
I've done this before and been quite happy with it. What do you find the problems to be, Grant?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 9, 2009 at 7:50 am
RBarryYoung (1/9/2009)
I've done this before and been quite happy with it. What do you find the problems to be, Grant?
My primary problem with it is maintenance. It becomes extremely difficult to determine which values belong to which pieces of the application. But it also presents problems in coding because, you can associate to values that are not actually meant to be associated to the entity in question. You can get around that by adding a bunch of extra code, to enforce the second layer of integrity that you've laid across the table... It's just messy. Yeah, having a hundred tables with 3 rows each (although it's never usually that bad) is messy too, but everything is clear, no special code or documentation is required. It's just easier. And, let's be clear, I am lazy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2009 at 9:39 am
I'll chime in that I have always used the separate tables. I don't recall reading either of the articles Grant mentioned so I must have slept through the days that they were published.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2009 at 8:29 am
There is one potential place where a unified form of a lookup table is handy, which is when the table contains a limited amount of static information. In that scenario, it may be worthwhile to consolidate the separation just to keep a static set simple. Usually, of course, those tables really have a model that contains ID, type, title of entry -- and the types are usually really sub-types of some larger related set. Separation, is, of course far easier in the long term, but this circumstance makes the unified form less reprehensible.
April 20, 2009 at 1:49 pm
Thankx guys.
I have got one interesting video on this. It worth viewing for lookpup tables manipulation. Find the video at below link
http://www.sqlbits.com/Agenda/event3/Small_Problem_-_Simple_Solution__Lookup_Tables/default.aspx
Regards,
Nitin
April 21, 2009 at 2:12 pm
I have seen both, and generally prefer the MUCK option - it may not be best practice, but I dislike having 100 tables with a handful of rows each and all with the same design (eg: PUK and Description), so I tend to opt for a PUK, CodeSet and Description.
By using and storing the PUK reference, SQL queries don't need to know what CodeSet the application used to then retrieve a description. The codeset allows the app to list just one specific set of values, while SQL queries can join to the MUCK table using a (globally) unique key.
Personally, I have not found the MUCK approach to cause any significant issues - except, many times, something that starts out as a simple list of lookup values grows to become a more complex object requiring more data to be captured. When that happens, it's much easier to change the database design if everything is kept in separate tables.
Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply