How to store DropDownList information in SQL

Question

I'm looking to store the contents of several dropdownlists in my SQL Server. Is it better to store them in 1 table per dropdown, or in a larger table?

My larger table would have schema like:

CREATE TABLE [dbo].[OptionTable](
    [OptionID] [int] IDENTITY(1,1) NOT NULL,
    [ListName] [varchar](100) NOT NULL,
    [DisplayValue] [varchar](100) NOT NULL,
    [Value] [varchar](100) NULL,
    [OptionOrder] [tinyint] NULL,
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) ON [PRIMARY]

And I would get the contents of 1 list by doing something like:

Select [columns]
From OptionTable
WHERE ListName = 'nameOfList'

So how can I decide? I know it will work like this, I'm just not sure if this is good practice or not? Will one way perform better? What about readability? Opinions appreciated.

Answer 1

I've worked in databases that had a single "super option table" that contained values for multiple drop down lists... it worked OK for the drop down list population, but when I needed to use those values for other reporting purposes, it became a pain because the "super option table" needed to be filtered based on the specific set of options that I needed, and it ended up in some ugly looking queries.

Additionally, down the road there were conditions that required an additional value to be tracked with one of the lists... but that column would need to be added to the whole table, and then all the other sets of options within that table would simply have a NULL for a column that they didn't care about...

Because of that, I'd suggest if you're dealing with completely distinct lists of data, that those lists be stored in separate tables.

Answer 2

The quick and easy:

CREATE TABLE [dbo].[Lists](
    [ListId] [int] IDENTITY(1,1) NOT NULL,
    [ListName] [varchar](100) NOT NULL,
    --these could be associated with lists or options, wasn't specified
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Options](
    [OptionId] [int] IDENTITY(1,1) NOT NULL,
    [ListId] [int] NOT NULL,
    [DisplayValue] [varchar](100) NOT NULL,
    [Value] [varchar](100) NULL,
    [OptionOrder] [tinyint] NULL,
    --these could be associated with lists or options, wasn't specified
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) ON [PRIMARY]

Get contents with

select Options.* --or a subset
from Options as o
join Lists as l
    on l.ListId=o.ListId and l.ListName = 'nameOfList'
order by o.OptionOrder

The (potentially: depends on your data) more optimized (particularly if one option appears in more than one list)

CREATE TABLE [dbo].[Lists](
    [ListId] [int] IDENTITY(1,1) NOT NULL,
    [ListName] [varchar](100) NOT NULL,
    --these could be associated with lists or options, wasn't specified
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Options](
    [OptionId] [int] IDENTITY(1,1) NOT NULL,
    [DisplayValue] [varchar](100) NOT NULL,
    [Value] [varchar](100) NULL,
    --these could be associated with lists or options, wasn't specified
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ListOptions](
    [OptionId] [int] NOT NULL,
    [ListId] [int] NOT NULL,
    [OptionOrder] [tinyint] NULL,
    --these could be associated with lists or options, wasn't specified
    [AssociatedDept] [int] NULL,
    [Other2] [nchar](10) NULL,
    [Other3] [nchar](10) NULL
) 

Get contents with

select Options.* --or a subset
from Options as o
join ListOptions as lo
    on lo.OptionId=o.OptionId
join Lists as l
    on l.ListId=lo.ListId and l.ListName = 'nameOfList'
order by lo.OptionOrder

On either, you'd want to index the foreign key columns.