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.

The biggest drawback with this approach is that it makes it difficult to have meaningful foreign keys from other tables to your `OptionTable`. — Eric Petroelje, Feb 08 '12 at 21:36
@EricPetroelje -Yes, I've already seen this actually. In a query I have to join the table to itself to get actual values (instead of keys). — MAW74656, Feb 08 '12 at 21:48
http://stackoverflow.com/q/8839026/456532 is a similiar question with fewer answers. — MAW74656, Feb 08 '12 at 22:07

score 7 · Accepted Answer · answered Feb 08 '12 at 21:34

7

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.

answered Feb 08 '12 at 21:34

Michael Fredrickson

36,839
5
92
109

-I feel like I can accomodate most of the option lists with DisplayValue, Value, and Order. Of course, I'm developing this application, and of the first 3 lists I'm making, I already needed a new column (associatedDepartment). – MAW74656 Feb 08 '12 at 21:45
@MAW74656 Exactly... then imagine what ten years and dozens of additional lists would require to be added. It might seem clean at first, but it gets messy later on. – Michael Fredrickson Feb 08 '12 at 21:51
-I have my query in a stored procedure so I just run something like `Exec getOptionList 'listName'` from client code to get the appropriate list. That way the code there is always the same. I rather like that part. – MAW74656 Feb 08 '12 at 21:57
1

@MAW74656 Well, if you want to keep the client code looking like that, you could create a view that is a `union` of all your different option tables, along with a `listName` for that specific table. Then your `getOptionList` could just select from that view instead. – Michael Fredrickson Feb 08 '12 at 22:02
-Then adding categories would just require updating the table (and union view). That could work. I guess I have to decide where I want the simplicity, on the server or in client code. – MAW74656 Feb 08 '12 at 22:04

score 2 · Answer 2 · answered Feb 08 '12 at 21:34

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.

-I'm not sure what your getting at here... What are you trying to demonstrate here? — MAW74656, Feb 08 '12 at 21:46
-And associateDept, Other2, Other3, all belong to an individual option, not to the list. — MAW74656, Feb 08 '12 at 21:48
I was implying (when I probably should have been suggesting) that you break the data into a lists table and an options table. The second suggestion was a further normalization of that data, that might have been useful if you had options that recurred across a variety of lists. That said, Michael Fredrickson's answer was the better. — devstruck, Feb 08 '12 at 21:58

How to store DropDownList information in SQL

2 Answers2

Linked