CODEWITHSUNDEEP
sqloptimizationstoragedatabase
Bala
Bala

Reputation: 390

Storing phone numbers in DB

this was asked in an interview where i had to store multiple phone numbers for each employee. I answered we could have a comma separated string of numbers. The next question was what if the size of the string becomes really long (suppose hypothetically 1000 numbers). Come up with a better solution. I was clueless. Could someone suggest the correct approach to the solution to this problem..

EDIT: i did suggest we freeze number of columns as some max number and insert aas per needs but it would lead to to many NULL values in most cases so that would have been a bad design.

EDIT: I just wanted to know if their does exist some other way of solving this problem other than adding a new table as suggested in one of the below comments (which i did tell as an answer). BTW is this some trick on the interviewer's part or does another solution actually exist?

Upvotes: 0

Views: 1273

Answers (3)

Aayushi Jain
Aayushi Jain

Reputation: 2879

Here phone number is a multi-valued attribute. You may use comma separated values and set upper bound and lower bound to a multi valued attribute for making sense, but as your interviewer asked for 1000 number entries then it will be good to provide atomicity to the table and create a new row for every phone number. This will increase the number of rows. You may then perform normalization. It is a case of multivalued dependency so you have to go till 4NF to come over this problem.

Upvotes: 1

songwei.beijing
songwei.beijing

Reputation: 9

you said you wanted to store long string into DB, I think the DB can not be reational DB, it can be nosql db instead. if the string is very long, you can choose to store the difference of each number instead of storing each of them wholly. and I think this way can save the disk space. eg. if you want to store 12345, 12346, 12347, 12358 you can store 12345, 1, 2, 3

Upvotes: -1

klaustopher
klaustopher

Reputation: 6951

How about a simple 1:n-Relation? Create a seperate table for the phone numbers like this:

Phone_Numbers(id, employee_id, phone_number_type, phone_number)

This way you can add thousands of phone numbers for each employee and not have a problem.

In general: It is never a good idea to store a comma-separated anything in a database field. You should read up on Database Normalization. Usually the 3NF is a good compromise to go

Upvotes: 5

Related Questions