I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories can be dynamic. How can this be designed in a database?
-
You also asked this question on StackOverflow. I gave a fairly detailed answer there, so I'd like to recommend that you have a look. I think it addresses an aspect of your design issue that you may not have considered.– Joel BrownCommented Aug 28, 2011 at 13:09
-
1I think your keyboard is missing the keys for period and comma...– RafaCommented Jun 30, 2012 at 20:36
Add a comment
|
4 Answers
Check out relational database design. It's an art and a science, and something that really can't be explained to the fullest in a post. But what you should do is create relationships between contacts and all possible data that will be related to it. This is most prevalent through the use of primary and foreign key relationships.
answered Aug 26, 2011 at 14:39
Have a look at "free data models" on databaseanswers.org. For example, "Contact Management"
Your question is too broad: you can hire me to do it of course :-)
Sounds like a pretty traditional relational database design.
CREATE TABLE Contact
(ContactId INT IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(255),
LastName nvarchar(255))
GO
CREATE TABLE Methods
(MethodId INT IDENTITY(1,1) PRIMARY KEY,
MethodDescription VARCHAR(25))
GO
INSERT INTO Methods
(MethodDescription)
VALUES
('Work Phone'), ('Home Email'), ('Home Phone'), ('Work Email')
GO
CREATE TABLE ContactMethods
(ContactMethodsId INT IDENTITY(1,1) PRIMARY KEY,
ContactId INT,
MethodId INT)
GO
You should look at Universal Data Models for Party Contact Mechanism, which allows you to store as many contacts and category of contacts that you want.
