All geek questions in one place

How to store DropDownList information in SQL

I am looking to store the contents of several dropdownlists in my SQL Server. Is it better to store them in 1 table per drop-down menu or in a larger table?

In my larger table there will be such a scheme as:

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 get the contents of 1 list, doing something like:

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

So how can I decide? I know that this will work, I’m just not sure if this is a good practice or not? Would one way work better? How about readability? Opinions were appreciated.

+4
source share
2 answers

I worked in databases that had one “super-parameter table” containing values ​​for several drop-down lists ... it worked fine for a top-down list, but when I needed to use these values ​​for other reporting purposes, it got pained because The “superparameter table” needed to be filtered out based on a specific set of parameters that I needed, and this turned out to be in some ugly search queries.

In addition, along the way, there were conditions requiring an additional value for tracking with one of the lists ... but this column should be added to the entire table, and then all other parameter sets within this table would simply have NULL for the column in which didn't care ...

Because of this, I would suggest, if you are dealing with completely different data lists, so that these lists are stored in separate tables.

+4
source

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 content using

 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

Optimized (potentially: depends on your data) (especially if one parameter appears in several lists)

 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 content using

 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

In any case, you want to index the foreign key columns.

+2
source

Source: https://habr.com/ru/post/1395495/

More articles:


All Articles