SQL: multi valued attributes

Question

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?

Answer 1

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.'

Answer 2

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 company with fields id, name, address, ... then you can create a table companyphones with 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.

Answer 3

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 SwitchboardPhone and FaxNumber for the main switchboard and the fax line, respectively.

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.

Answer 4

There're some possibilities in different implementations of RDBMS.

For example, in PostgreSQL you can use array or hstore or even JSON (in 9.3 version):

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

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?

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

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

You can also create xml indexes on xml type column.