SQL：多值属性

SQL: multi valued attributes

提问by user1150176

I created a table that contains information about a company. One attribute is their telephone number. A company can have many telephone numbers.

我创建了一个包含公司信息的表。一个属性是他们的电话号码。一个公司可以有多个电话号码。

How do I create multi-valued attributes in SQL?

如何在 SQL 中创建多值属性？

回答by Oleg Dok

In a separate table like:

在一个单独的表中，如：

CREATE TABLE Company
(
  Id int identity primary key, 
  Name nvarchar(100) not null UNIQUE --UNIQUE is optional
)
GO
CREATE TABLE CompanyPhones
(
  Id int identity primary key, 
  Phone nvarchar(100) not null, 
  CompanyId int NOT NULL REFERENCES Company(Id) ON DELETE CASCADE
)

How to use these structures:

如何使用这些结构：

SELECT CompanyPhones.Phone
FROM Company
JOIN CompanyPhones
  ON Company.Id = CompanyPhones.CompanyId
WHERE Company.Name=N'Horns and Hoogs Ltd.'

回答by Sergey Kudriavtsev

There is generally no such thing as multi-valued attribute in relational databases.

关系数据库中通常没有多值属性这样的东西。

Possible solutions for your problem:

您的问题的可能解决方案：

1. Create a separate table for storing phone numbers which references your company table by primary key and contains undefinite number of rows per company.

   For example, if you have table companywith fields id, name, address, ...then you can create a table companyphoneswith fields companyid, phone.

2. (NOT recommended in general, but if you only need to show a list of phones on website this might be an option) Storing telephones in a single field using varchar(...) or text and adding separators between numbers.

1. 创建一个单独的表来存储电话号码，该表通过主键引用您的公司表，并包含每个公司的不确定行数。

   例如，如果您有company带有字段的表，id, name, address, ...那么您可以创建一个companyphones带有 fields的表companyid, phone。

2. （一般不推荐，但如果您只需要在网站上显示电话列表，这可能是一个选项）使用 varchar(...) 或文本将电话存储在单个字段中并在数字之间添加分隔符。

回答by Sergey Kudriavtsev

In addition to Oleg and Sergey's answers, a third option might be to create multiple phone fields on the company table - for example, as SwitchboardPhoneand FaxNumberfor the main switchboard and the fax line, respectively.

除了奥列格和谢尔盖的回答，第三个选择可能是对公司表创建多个手机领域-例如，作为SwitchboardPhone和FaxNumber分别用于主交换机和传真线路。

This type of solution is generally regarded as a form of denormalisation, and is generally only suitable where there is a small number of multiple options, each with a clearly defined role.

这种类型的解决方案通常被认为是一种反规范化形式，通常只适用于存在少量多个选项的情况，每个选项都有明确定义的作用。

So, for example, this is quite a common way to represent landline and mobile/cellphone numbers for a contact list table, but would be thoroughly unsuitable for a list of all phone extensions within a company.

因此，例如，这是表示联系人列表表的固定电话和移动/手机号码的一种非常常见的方法，但完全不适合公司内所有电话分机的列表。

回答by Roman Pekar

There're some possibilities in different implementations of RDBMS.

RDBMS 的不同实现有一些可能性。

For example, in PostgreSQLyou can use arrayor hstoreor even JSON (in 9.3 version):

例如，在PostgreSQL 中，您可以使用数组或hstore甚至JSON（在 9.3 版本中）：

create table Company1 (name text, phones text[]);

insert into Company1
select 'Financial Company', array['111-222-3333', '555-444-7777'] union all
select 'School', array['444-999-2222', '555-222-1111'];

select name, unnest(phones) from Company1;

create table Company2 (name text, phones hstore);

insert into Company2
select 'Financial Company', 'mobile=>555-444-7777, fax=>111-222-3333'::hstore union all
select 'School', 'mobile=>444-999-2222, fax=>555-222-1111'::hstore;

select name, skeys(phones), svals(phones) from Company2    

sql fiddle demo

sql fiddle demo

You can also create indexes on these fields - https://dba.stackexchange.com/questions/45820/how-to-properly-index-hstore-tags-column-to-faster-search-for-keys, Can PostgreSQL index array columns?

您还可以在这些字段上创建索引 - https://dba.stackexchange.com/questions/45820/how-to-properly-index-hstore-tags-column-to-faster-search-for-keys，可以 PostgreSQL 索引数组列？

In SQL Server, you can use xml datatype to store multivalues:

在SQL Server 中，您可以使用 xml 数据类型来存储多值：

create table Company (name nvarchar(128), phones xml);

insert into Company
select 'Financial Company', '<phone type="mobile">555-444-7777</phone><phone>111-222-3333</phone>' union all
select 'School', '<phone>444-999-2222</phone><phone type="fax">555-222-1111</phone>'

select
    c.name,
    p.p.value('@type', 'nvarchar(max)') as type,
    p.p.value('.', 'nvarchar(max)') as phone
from Company as c
    outer apply c.phones.nodes('phone') as p(p)

sql fiddle demo

sql fiddle demo

You can also create xml indexeson xml type column.

您还可以在 xml 类型列上创建 xml 索引。