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.