CODEWITHSUNDEEP
mysqldatabasedatabase-design
Harjeet Jadeja
Harjeet Jadeja

Reputation: 1604

how to deal with multiple values for a single field in table?

i have 1 table in phpmyadmin users which contains below fields.

users:

uid | name | contact.no

There can be more then one contact number for a single user.
One way to solve it is using one more table for contact number and pass its primary key to users table.
Is there any other way other then this one. Can we implement array structure in contact.no field?

Upvotes: 0

Views: 1526

Answers (2)

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7880

You wouldn't have to create multiple tables for each type of entry, just a more robust table structure. Make sure that the information that needs to be normalized is in a consistent format.

Users: uid | name | username

1,Bob,bcratchet

Info: iid | itype | icontent | uid

1,cell,000.000.0000
2,home,000.000.0000    
3,home_addr,1234 Anystreet, anytown USA
4,work_addr,4567 Anystreet, anytown USA

select * from Users u,Info i where u.uid=i.uid and name="Bob";

Pull it into a multidimensional array in any application and you're good to go.

edit* Ideally it would go further and show a table like itypes where you would further normalize the types like so:

itypes: itype_id | itype

1,cell
2,home
3,home_addr
4,work_addr

Then in the Info table it would say "itype_id" instead of "itype."

Upvotes: 1

Hanky Panky
Hanky Panky

Reputation: 46900

You could put commas over there and save multiple numbers but then it kills the whole concept of an RDMS and Normalization. That will not be a good database design. So it is advisable to normalize your table and not store such multiple information in one field. Database doesn't really stress itself if you have 1 more table.

A very well written explanation can be found Here on Microsoft Website

Upvotes: 3

Related Questions