🔒 Locked

Multiple attributes in one record, sql

Started by _Madman_ Oct 25, 2005 at 5:00 PM 1 replies 550+ views

Original Post

Oct 25, 2005 at 5:00 PM

How can you store variable number of attributes in one record via SQL. Like phone numbers. A guy can have 1 or 2 or like 10 numbers...

Arild Fines

Oct 25, 2005 at 5:26 PM

In this case, you have a one-to-many relationship between persons and phone numbers. The "normal" way to deal with this is to introduce a second table, just for the phone numbers.

IE:

CREATE TABLE Person(   SSN Integer PRIMARY KEY,   FirstName VARCHAR(50),   LastName VARCHAR(50)   ...);CREATE TABLE PhoneNumber(   SSN Integer,   PhoneNumber VARCHAR(20));

To retrieve a person with all her/his phone numbers, you then do a join:

SELECT Person.FirstName, Person.LastName, PhoneNumber.PhoneNumber FROM Person, PhoneNumber WHERE Person.SSN='?' AND Person.SSN = PhoneNumber.SSN;

(Don't shoot me, I don't do much SQL. The principle's good, though.)

sit

Oct 25, 2005 at 5:32 PM

Quote:
Original post by Arild Fines
In this case, you have a one-to-many relationship between persons and phone numbers. The "normal" way to deal with this is to introduce a second table, just for the phone numbers.

or you could be evil and cram them all into a single nvarchar field

but having a join table is a better approach... one we are not using

we have fields for 3 different types of phone number [all nullable], their home, cell and fax number... if they have more than this, well... its their decision which ones we treat as which. [do you really need to store all 10 of somebody's phone numbers?]

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.