27

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?

Parth Patel
  • 915
  • 11
  • 33
Krutika Sonawala
  • 1,065
  • 1
  • 12
  • 30
  • 1
    Add the table description and sample records to descript what you have and what you want.. – Jens Jul 21 '14 at 12:41
  • You can also define a `text field` and you will insert an `array` witch contains all your phones numbers. – ji_bay_ Jul 21 '14 at 12:42
  • 1
    Can you just store the phone numbers as comma separated numbers and then later split the phone numbers using the comma as the delimiter? – The One and Only ChemistryBlob Jul 21 '14 at 12:43
  • You can use the JSON column type and store multiple phone numbers if you have to use just one column or if your data isn't frequently going to change. For all other cases, a seperate table that stores the numbers is a better solution. – aalaap May 29 '19 at 09:56

8 Answers8

30

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.

Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
22

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)
    )
semao
  • 1,757
  • 12
  • 12
  • 3
    I guess this link is helpful for understanding why this answer is correct : http://en.wikipedia.org/wiki/First_normal_form – Vartlok Jul 21 '14 at 12:44
2

This is not a best approach because your database will become ambiguous and will fail 1st normal form. Simple you have to make another column and in this your first column primary key act as a foreign key. And then using joins simply you can do it.

As mentioned below

TylerH
  • 20,799
  • 66
  • 75
  • 101
Muhammad Wasim Akram
  • 655
  • 11
  • 15
  • 1
    Technically, using JSON form does not break 1NF - see [here](https://softwareengineering.stackexchange.com/questions/415833/what-is-the-normal-form-of-json). Additionally, surely this table format would be inefficient had the customer had 500 telephone numbers, for example. – Joe Moore Mar 05 '22 at 15:22
1

As far as PostgreSQL is concerned, You can store XML data by using xml type which can store well-formed "documents". This URL might be helpful:

https://www.postgresql.org/docs/9.1/datatype-xml.html

TeaSea
  • 55
  • 6
0

The best way is:

  1. serialize your phone.no array,
  2. store in your table,
  3. deserialize when you want to get phone.no.
leftjoin
  • 36,950
  • 8
  • 57
  • 116
Ali Mirza
  • 19
0

For storing multiple values in single column, you can have json or jsonb column in your table, so that you can store multiple values as json array in column.

["+9145454456343", "+9123423423423"]

And at the time of fetching the values from database you can write parser or converter, to convert the json array into List

Jitendra Nandre
  • 121
  • 7
  • 1
    Does this honour the 1st NF ? If not, is it okay to break the 1NF in some cases ? – Shreehari Nov 10 '22 at 18:04
  • Yes Its ok. Database only come up with this new features. Postgres provide this json/jsonb columns. Buts depending on our usecase we should use it. Not in every case. – Jitendra Nandre Nov 24 '22 at 05:01
-3

You can store multiple data as delimiter separated values. Use pipe (|) or tilt (~) as the delimiter. And when we are inserting new value or updating existing value, check if the phone number already exists. If there is already some data present in the phone number column, just append it with delimiter + new phone number.

AshutoshPujari
  • 136
  • 2
  • 9
-3

You could just add them comma seperated. If you're doing a webapp, you could make your phone column a string and json encode them, then you'd have support for endless amount of phone numbers :)

Brunis
  • 1,073
  • 8
  • 12