SQL 单列表是好的设计吗？

Is a one column table good design?

提问by Aheho

It it ok to have a table with just one column? I know it isn't technically illegal, but is it considered poor design?

一张只有一列的表格可以吗？我知道这在技术上并不违法，但它被认为是糟糕的设计吗？

EDIT:

编辑：

Here are a few examples:

这里有一些例子：

• You have a table with the 50 valid US state codes, but you have no need to store the verbose state names.
• An email blacklist.

• 您有一个包含 50 个有效美国州代码的表，但您无需存储详细的州名。
• 电子邮件黑名单。

Someone mentioned adding a key field. The way I see it, this single column WOULD be the primary key.

有人提到添加一个关键字段。在我看来，这一列将是主键。

采纳答案by Erik Funkenbusch

Yes, it's certainly good design to design a table in such a way as to make it most efficient. "Bad RDBMS Design" is usually centered around inefficiency.

是的，以最高效的方式设计一张桌子当然是一个很好的设计。“糟糕的 RDBMS 设计”通常以低效率为中心。

However, I have found that most cases of single column design could benefit from an additional column. For example, State Codes can typically have the Full State name spelled out in a second column. Or a blacklist can have notes associated. But, if your design really does not need that information, then it's perfectly ok to have the single column.

但是，我发现单列设计的大多数情况都可以从额外的列中受益。例如，州代码通常可以在第二列中拼出完整的州名。或者黑名单可以关联注释。但是，如果您的设计确实不需要该信息，那么拥有单列是完全可以的。

回答by Quassnoi

In terms of relational algebrathis would be a unary relation, meaning "this thing exists"

就relational algebra这而言，这将是一个一元关系，意思是“这个东西存在”

Yes, it's fine to have a table defining such a relation: for instance, to define a domain.

是的，有一个定义这种关系的表是很好的：例如，定义一个域。

The values of such a table should be natural primary keys of course.

这种表的值当然应该是自然的主键。

A lookup table of prime numbersis what comes to my mind first.

prime numbers我首先想到的是查找表。

回答by Spencer Ruport

I've used them in the past. One client of mine wanted to auto block anyone trying to sign up with a phone number in this big list he had so it was just one big blacklist.

我过去使用过它们。我的一位客户想要自动阻止任何试图使用他拥有的这个大名单中的电话号码注册的人，因此这只是一个大黑名单。

回答by Kevin

If there is a valid need for it, then I don't see a problem. Maybe you just want a list of possibilities to display for some reason and you want to be able to dynamically change it, but have no need to link it to another table.

如果确实需要它，那么我认为没有问题。也许您只是出于某种原因想要显示一个可能性列表，并且您希望能够动态更改它，但不需要将其链接到另一个表。

回答by Doliveras

One case that I found sometimes is something like this:

我有时发现的一种情况是这样的：

Table countries_id, contains only one column with numeric ID for each country.

表country_id，只包含一列，每个国家的数字 ID。

Table countries_description, contains the column with country ID, a column With language ID and a column with the localized country name.

表countries_description包含带有国家 ID 的列、带有语言 ID 的列和带有本地化国家名称的列。

Table company_factories, contains information for each factory of the company, including the country in Wich is located.

表company_factories，包含公司每个工厂的信息，包括所在国家/地区。

So to maintain data coherence and language independent data in the tables the database uses this schema with tables with only one column to allow foreign keys without language dependencies.

因此，为了维护表中的数据一致性和语言独立数据，数据库使用这种模式和只有一列的表，以允许没有语言依赖性的外键。

In this case I think the existence of one column tables are justified.

在这种情况下，我认为存在一列表是合理的。

Edited in response to the comment by:Quassnoi

根据评论编辑：Quassnoi

_{(source: ggpht.com)}

_{（来源：ggpht.com）}

In this schema I can define a foreign key in the table company_factories that does not require me to include Language column on the table, but if I don't have the table countries_id, I must include Language column on the table to define the foreign key.

在这个模式中，我可以在表 company_factories 中定义一个外键，它不需要我在表中包含 Language 列，但是如果我没有表 country_id，我必须在表中包含 Language 列来定义外键.

回答by Jeremy Bourque

There would be rare cases where a single-column table makes sense. I did one database where the list of valid language codes was a single-column table used as a foreign key. There was no point in having a different key, since the code itself was the key. And there was no fixed description since the language code descriptions would vary by language for some contexts.

在极少数情况下，单列表有意义。我做了一个数据库，其中有效语言代码列表是用作外键的单列表。使用不同的密钥毫无意义，因为代码本身就是密钥。并且没有固定的描述，因为对于某些上下文，语言代码描述会因语言而异。

In general, any case where you need an authoritative list of values that do not have any additional attributes is a good candidate for a one-column table.

通常，任何需要没有任何附加属性的权威值列表的情况都是单列表的良好候选者。

回答by A. I. Breveleri

I use single-column tables all the time -- depending, of course, on whether the app design already uses a database. Once I've endured the design overhead of establishing a database connection, I put all mutable data into tables where possible.

我一直使用单列表——当然，这取决于应用程序设计是否已经使用了数据库。一旦我忍受了建立数据库连接的设计开销，我就会尽可能将所有可变数据放入表中。

I can think of two uses of single-column tables OTMH:

我可以想到单列表OTMH的两种用途：

1) Data item exists. Often used in dropdown lists. Also used for simple legitimacy tests.

1) 数据项存在。常用于下拉列表。也用于简单的合法性测试。

Eg. two-letter U.S. state abbreviations; Zip codes that we ship to; words legal in Scrabble; etc.

例如。两个字母的美国州缩写；我们运送到的邮政编码；拼字游戏中的合法词；等等。

2) Sparse binary attribute, ie., in a large table, a binary attribute that will be true for only a very few records. Instead of adding a new boolean column, I might create a separate table containing the keys of the records for which the attribute is true.

2) 稀疏二进制属性，即在一个大表中，一个二进制属性只对极少数记录为真。我可能不会添加新的布尔列，而是创建一个单独的表，其中包含属性为真的记录的键。

Eg. employees that have a terminal disease; banks with a 360-day year (most use 365); etc.

例如。患有绝症的员工；一年 360 天的银行（大多数使用 365）；等等。

-Al.

-阿尔。

回答by Agnel Kurian

No problem as long as it contains unique values.

只要它包含唯一值就没有问题。

回答by HLGEM

Mostly I've seen this in lookup type tables such as the state table you described. However, if you do this be sure to set the column as the primary key to force uniqueness. If you can't set this value as unique, then you shouldn't be using one column.

大多数情况下，我在查找类型表（例如您描述的状态表）中看到了这一点。但是，如果您这样做，请务必将该列设置为主键以强制唯一性。如果您不能将此值设置为唯一值，则不应使用一列。

回答by Brendan Enrick

I would say in general, yes. Not sure why you need just one column. There are some exceptions to this that I have seen used effectively. It depends on what you're trying to achieve.

我会说一般来说，是的。不知道为什么你只需要一列。我已经看到有效地使用了一些例外情况。这取决于您要实现的目标。

They are not really good design when you're thinking of the schema of the database, but really should only be used as utility tables.

当您考虑数据库的架构时，它们并不是真正好的设计，但实际上应该仅用作实用程序表。

I've seen numbers tablesused effectively in the past.

我看到过去有效地使用了数字表。