How to store multiple values ​​in one field in sql database

Question

My problem is that I want to save a person with multiple phone numbers in the database. for one variable for a number, only one number for each will be stored. Now, if I want to add another phone number, it creates a new new entry with the same details, but with a different number. I want to show all these numbers together. can anyone help?

Answer 1

You can use the second table to store numbers and a link using a foreign key:

PersonTable: PersonId, Name, etc.. 

The second table will contain the numbers ...

 NumbersTable: NumberId, PersonId(fk), Number 

Then you can get 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 used LEFT JOIN here if the person did not provide their number. Also, this is just pseudo-code , so do not use the table in the name.

Answer 2

You must 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) ) 

Answer 3

You can store multiple data as delimiter values. Use pipe (|) or slope (~) as a separator. And when we insert a new value or update an existing value, check if the phone number already exists. If there is already data in the phone number column, just add it with a separator + a new phone number.

Answer 4

This is not the best approach, because your database will become ambiguous and will not maintain normalization. You just have to make another column, in which case your first primary key column acts as a foreign key. And then, using connections, you can just do it. As mentioned below

Answer 5

You can simply add them to a comma. If you are doing webapp, you can make your phone column a string and json encode them, then you will have support for an infinite number of phone numbers :)

Answer 6

The best way:

• serialize your.no array
• save to your table,
• deserialize when you want to get phone.no.