Store array in SQL Server 2008

Question

I am developing a contact manager application using SQL Server 2008 (service-based database) . Most contacts have several emails or several phone numbers. So is there a way to store an array as a datatype in SQL Server? Or does anyone have an alternative to this way?

Answer 1

You'll want to create separate tables, with a row per contact number or email address.

CREATE TABLE Contacts (contactId int, name varchar(128), etc, etc
CREATE TABLE ContactEmail (contactId int, emailAddress varchar(128), etc
CREATE TABLE ContactPhone (contactId int, phoneNumber varchar(128), etc

This will allow you to modify individual numbers/emails, remove them, add them, etc, without requiring an external program to unpack an array.

But if you really want to store it denormalized, you could transform the array into a delimited string. . put a delimiter between each email address (with the appropriate magic to make sure an address doesn't already contain the delimiter) then split it on the way back out.

Answer 2

No there isn't an array type in SQL.

You should create a phonenumbers table and store one phone number per row use and use a foreign key (called, for example, person_id) to refer to the person table.