Look up table or not for application drop downs?

  • 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],


    [EndDate] [smalldatime] )

  • I prefer to have a specific table for a specific purpose. It makes RI easier to enforce.

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.


  • 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.


Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply