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?
Best Answer
In a separate table like:
CREATE TABLE Company(Id int identity primary key, Name nvarchar(100) not null UNIQUE --UNIQUE is optional)GOCREATE 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.PhoneFROM CompanyJOIN CompanyPhonesON Company.Id = CompanyPhones.CompanyIdWHERE Company.Name=N'Horns and Hoogs Ltd.'There is generally no such thing as multi-valued attribute in relational databases.
Possible solutions for your problem:
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 fieldsid, name, address, ...then you can create a tablecompanyphoneswith fieldscompanyid, phone.(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.
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.
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 Company1select 'Financial Company', array['111-222-3333', '555-444-7777'] union allselect 'School', array['444-999-2222', '555-222-1111'];select name, unnest(phones) from Company1;create table Company2 (name text, phones hstore);insert into Company2select 'Financial Company', 'mobile=>555-444-7777, fax=>111-222-3333'::hstore union allselect '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 Companyselect 'Financial Company', '<phone type="mobile">555-444-7777</phone><phone>111-222-3333</phone>' union allselect 'School', '<phone>444-999-2222</phone><phone type="fax">555-222-1111</phone>'selectc.name,p.p.value('@type', 'nvarchar(max)') as type,p.p.value('.', 'nvarchar(max)') as phonefrom Company as couter apply c.phones.nodes('phone') as p(p)sql fiddle demo
You can also create xml indexes on xml type column.