How to store multiple values in single field in SQL database?

Question

My problem is, I want to store a person with multiple phone numbers in the database. for a single variable for number will store only one number for each.

Now if I want to add another phone number it is creating another new record with the same details but the different number.

I want to display all these numbers together. Can anyone help?

Answer 1

You could use a second table to store the numbers, and link back with a Foreign Key:

PersonTable: PersonId, Name, etc..

The second table will hold the numbers...

NumbersTable: NumberId, PersonId(fk), Number

You could then get the numbers like this...

SELECT p.Name, n.Number from PersonTable p Left Join NumbersTable n
on p.PersonId = n.PersonId

This is a simple example. I have used a LEFT JOIN here in case a person doesn't supply their number. Also, this is just pseudo code, so don't use Table in the name.

Answer 2

You should create separate tables for Person and PhoneNumber.

CREATE TABLE Person(PersonId int IDENTITY(1,1) PRIMARY KEY)

CREATE TABLE Phone(
    PersonId int,
    PhoneNumber varchar(20),
    CONSTRAINT PK_Phone PRIMARY KEY(PersonId,PhoneNumber),
    CONSTRAINT FK_PersonId FOREIGN KEY(PersonId) REFERENCES Person(PersonId)
    )