December 10, 2009 at 5:13 pm
Hi all
I am working on an application that will have 200+ drop downs that are related to client data.
I am confused as to the best design methods. I was thinking of using 2 tables to hold the data that will populate the dropdowns instead of a reference table for each dropdown, not to mention the procs. I have read SEVERAL articles that say this is bad and a few that say this method will work.
Not wanting to start a war, what would be considered best practice for holding the data for each of the dropdowns? Thank you for your input.
This is close to what I was going to use....
CREATE TABLE [dbo].[Ref_A](
[Group] [int] NOT NULL,
[Description] [varchar](50) NOT NULL,
[UserId] [varchar](50) NOT NULL,
[LastUpdated] [smalldatetime] )
CREATE TABLE [dbo].[Ref_B](
[Group] [int] NOT NULL,
[Code] [int] NOT NULL,
[Description] [varchar](50) NOT NULL,
[UserId] [varchar](50) NOT NULL,
[LastUpdated] [smalldatetime],
[StartDate][smalldatetime],
[EndDate] [smalldatime] )
December 10, 2009 at 6:07 pm
I prefer to have a specific table for a specific purpose. It makes RI easier to enforce.
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
December 14, 2009 at 8:30 am
I've seen major applications done both ways and the single, purpose built table style of design just flat out works better. It's easier to maintain the structures, there are fewer problems, data integrity is better, data maintenance is easier. It is messy having tiny tables all over the place, but they sure work well.
"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
December 15, 2009 at 12:02 pm
I've also seen it done both ways, and of course each has advantanges.
Personally, I prefer a single table for simple code/description lists (or two tables, as you suggested) and have never found any real issues with using this method. With 200+ lists I would certainly consider this option.
If you do go with the one/two table option I suggest you carefully consider what groups/lists you have - it is often the case that, as an application grows, some of these lists become larger entities that won't fit that model anymore. If that happens, then changing your database design might have a greater capacity to break the application - if that is likely then separate tables should probably be created.
Chris
February 6, 2010 at 6:21 pm
Both approaches have advantages, both have drawbacks. Which is best depends on a lot of factorsd in the system you are building, and it may be that given your goal each is as good as the other.
If you want to be able to support several different versons of the thing for different customers, with only small differences between the versions, the "single" table approach may be best ("single" actually means as many as are required for acceptable normalisation, not literally just one) because it is easy to be flexible.
If the dropdowns can refer to each other the "single" table approach avoids having reflexive T-SQL, having table names embedded in text columns - allows referential integrity to be checked.
If the dropdowns are sufficently different to need to have different attribute sets, so that they wouldn't use the same columns as each-other, the one table for each approach will avoid having columns which are non-null only in a small number of rows (so reduce disc occupancy and ram working set, and reduce disc throughput needed).
If none of the consitions listed above apply, there probably isn't much to choose between the two approaches.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply