如何使用表中不同数量的列设计数据库

Let us say that you are creating a system to store characteristics of different countries. There will be same basic columns like name, population, capital city etc. But let us say in addition to it you want to store some country specific information like highest mountain, nearest ocean, most famous food etc. These columns will be different for each country.

我们假设您正在创建一个存储不同国家/地区特征的系统。将有相同的基本栏目,如姓名,人口,首都等。但是,除了它之外,我们还要说存储一些国家特定的信息,如最高的山峰,最近的海洋,最着名的食物等。这些列将是不同的国家。

How can this be done using a relational database like MySQL. I know this is easier using a schema-less NoSQL database like MongoDB where each country can be stored as a separate document. But can something like this be done using relational DBs?

如何使用像MySQL这样的关系数据库来完成。我知道使用像MongoDB这样的无模式NoSQL数据库可以更容易地将每个国家/地区存储为单独的文档。但是可以使用关系数据库完成这样的事情吗?

8 个解决方案

#1

With only text fields you need two additional tables:

只有文本字段,您需要两个额外的表:

properties (contains the name of a property, eg "highest mountain")

属性(包含属性的名称,例如“最高峰”)

country_properties (contains values for country-property pairs: eg: id of country "austria", id of property "highest mountain", "name of the mountain")

country_properties(包含国家/地产对的值:例如:国家/地区“奥地利”的ID,“最高山”的属性ID,“山的名称”)

Alternatively, if there are only a couple of properties, simply store NULL for unknown values.

或者,如果只有几个属性,则只需为未知值存储NULL。

#2

It can. As I learned today by asking another question on SO, this is called EAV (for Entity-Attribute-Value model). I found an interesting explanation about it on wikipedia.

它可以。正如我今天通过询问关于SO的另一个问题所学到的,这被称为EAV(用于实体 - 属性 - 值模型)。我在*上找到了一个有趣的解释。

#3

You are really not supposed to be doing that with a standard relational database. Instead store the extra data in separate tables and use a foreign key referencing the country table.

你真的不应该用标准的关系数据库来做这件事。而是将额外数据存储在单独的表中,并使用引用该国家/地区表的外键。

Having columns that are only sometimes used is generally a violation of relational integrety. Sometimes it's neccessary for performance reasons, but if that is not a concern to you I would highly suggest going with the most appropriate relational model.

拥有仅有时使用的列通常会违反关系完整性。有时候出于性能原因这是必要的,但如果你不关心这一点,我强烈建议你选择最合适的关系模型。

#4

If the columns are truly different for each country, then create a new table called country_field with the following columns

如果每个国家/地区的列确实不同,则使用以下列创建名为country_field的新表

country_id (FK to your country table)

country_id(FK到您的国家/地区表)

field_name varchar
field_value varchar

Store your country specific attributes in this table with one row for each country specific field.

在此表中存储您的国家/地区特定属性,每个国家/地区特定字段都有一行。

#5

I have a similar scenario with an app requiring mySQL, what I found the most flexable option was for us was to split the data into multiple tables, for example we may have a table called country_register that has

我有一个类似的应用程序需要mySQL的情况,我发现最灵活的选项对我们来说是将数据分成多个表,例如我们可能有一个名为country_register的表,

country_id (int primary key) | country_name

Then we have another table called say country_data that has

然后我们有另一个名为country_data的表

tbl_id (int primary key) | country_id (int foreign key) | country_property (varchar index) | country_data (text indexed as fulltext)

Basically the country_property is a reference for you to get data out, so it could be for example "population" and the country_data would then have the actual data you want.

基本上country_property是您获取数据的参考,因此它可以是例如“population”,country_data将具有您想要的实际数据。

You would then use a JOIN and each row will have all of the data you need. This is the most flexible structure using mySQL I know of and it works well for these types of tasks.

然后,您将使用JOIN,每行将包含您需要的所有数据。这是我所知道的使用mySQL的最灵活的结构,它适用于这些类型的任务。

I hope this helps.

我希望这有帮助。

#6

What you are describing is a Super-Type *Sub-Type* of a data structure. The Super-Type is what is common amongst all the data (in your case Countries). The Sub-Type is what is unique to each group of data (in your case countries). You would have one super-type tabl**e and **several sub-type tables. The sub-type tables contain FKeys that link back to the super-type table.

您所描述的是数据结构的超类型*子类型*。 Super-Type是所有数据中的常见数据(在您的国家/地区)。子类型是每组数据(在您的国家/地区)中唯一的。您将拥有一个超类型表格和几个子类型表格。子类型表包含链接回超类型表的FKeys。

This lets you query all by super-type and then do a drill down by sub-type.

这使您可以通过超类型查询所有内容,然后按子类型向下钻取。

*strong text*Subtypes that come to mind for countries are:
Mountanous
LandLocked
OceanView

*强文*各国想到的子类型:Mountanous LandLocked OceanView

You could even sub them out by continent: NorthAmerica
SouthAmerica
Asia
Oceania

您甚至可以将它们分散到大陆:NorthAmerica SouthAmerica Asia Oceania

#7

Let's take the mountains as an example:

我们以山为例:

CREATE TABLE `countries` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `mountains` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_id` INT(11) UNSIGNED NOT NULL,
  `name` VARCHAR(255),
  `height` INT(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

You can than create a SELECT-query, to get the highest mountain of each country, by doing something like:

您可以创建一个SELECT查询,通过执行以下操作来获得每个国家/地区的最高峰:

SELECT c.name, m.name, MAX(m.height) as height
FROM mountains m
JOIN countries c
ON c.id = m.country_id
GROUP BY m.country_id;

#8

Here we have three strategies:

这里我们有三个策略:

Full-meta design, the values of nullable attributes for countries would be put into value-collection table. For example:

全元设计,各国可空属性的价值将被纳入价值收集表。例如:

country(country_id, non-null-attr-1, non-null-attr-2, non-null-attr-....) meta_attr(attr_id, attr_desc)(may more complex if you need I18N) attr_value(country_id, attr_id, attr_value)

country(country_id,非null-attr-1,非null-attr-2,非null-attr -....)meta_attr(attr_id,attr_desc)(如果需要I18N,可能会更复杂)attr_value(country_id, attr_id,attr_value)
Partially meta design, using sub-class of table to reference the main table of country. Such method is usable if you could classify a certain data instance to a collection of non-null attributes. For example:

部分元设计,使用表的子类来引用国家的主表。如果您可以将某个数据实例分类为非null属性的集合,则此方法可用。例如:

country(country_id, non-null-attr-1, non-null-attr-2, non-null-attr-....) specific_type_country(country_id, non-null-attr-1, non-null-attr-2, non-null-attr-...)

country(country_id,non-null-attr-1,non-null-attr-2,non-null-attr -....)specific_type_country(country_id,non-null-attr-1,non-null-attr-2 ,非null-attr -...)
All attributes in main table of countries, this method is only viable if your don't need to add new attribute into country from system. For example:

国家主表中的所有属性,只有在您不需要从系统向国家/地区添加新属性时,此方法才可行。例如:

country(country_id, non-null-attr-1, non-null-attr-2, non-null-attr-...., nullable-attr-1, nullable-2, nullable-attr-...)

country(country_id,非null-attr-1,非null-attr-2,非null-attr -....,nullable-attr-1,nullable-2,nullable-attr -...)

When I am designing under such scenario, I used to consider the performance of queries running on such data.

当我在这种情况下进行设计时,我曾经考虑过在这些数据上运行的查询的性能。

If the queries are the list of countries for all possible attributes, using No.3 is better.

如果查询是所有可能属性的国家/地区列表,则使用No.3更好。

If the queries target a certain classes of countries, say, a list of countries have nearest ocean(which this attribute can't be null). No.2 is better.

如果查询针对特定类别的国家/地区,例如,国家/地区的列表具有最近的海洋(此属性不能为空)。 2号比较好。

If the queries need the detail information for a country at a time, No.1 is better.

如果查询一次需要一个国家/地区的详细信息,则No.1更好。

Of course, you may mix any of above three strategies to design a suitable solution for your possible queries.

当然,您可以混合以上三种策略中的任何一种来为您的可能查询设计合适的解决方案。

Assume that the "most famous food"(nullable) would be needed on whatever queries, put this attribute into main table of country.

假设在任何查询中都需要“最着名的食物”(可空),将此属性放入国家主表中。

Assume that the "nearest ocean" is needed in a few of queries, put this attribute into sub-class of country table.

假设在一些查询中需要“最近的海洋”,将此属性放入国家表的子类中。

Assume that the "highest mountain name", "The average temperature of highest mountain" is needed for queries that retrieve only one row at most(say, queried by primary key), put this attribute into meta-table.

假设最多只检索一行的查询(例如,通过主键查询)需要“最高山名”,“最高山的平均温度”,将此属性放入元表中。

#1