如何在SQL中存储DropDownList信息 [英] How to store DropDownList information in SQL

问题描述

我打算存储几个下拉列表的内容在我的SQL Server。最好将它们存储在每个下拉列表1个表中，还是放在较大的表中？

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]

我可以通过执行以下操作获得1列表的内容：

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.

推荐答案

我在数据库中使用过单个超级选项表，其中包含多个下拉列表的值列表...它对下拉列表填充起作用，但是当我需要使用这些值用于其他报告目的时，它变得很痛苦，因为超级选项表需要根据特定的选项集进行过滤我需要它，它最终在一些丑陋的查询。

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.

此外，在路上有条件，需要一个额外的值跟踪列表之一。 ..但是该列需要添加到整个表，然后该表中的所有其他选项集只会有一个 NULL 关心...

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.

这篇关于如何在SQL中存储DropDownList信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！