Dynamic contacts database

I'm trying to create database for contacts I need for every contact to have many catogries Like home Office Emails Phones And in every category 开发者_Python百科can add more one value Also the categories can be dynamic How can this be designed in database

---

It seems to me that you have two kinds of categorization that you need to be concerned with. First you have the purpose of the contact, i.e. Home or Office (there could be others, such as Head Office, Emergency Contact, Vacation Home,...) and the other is the medium of the contact, i.e. E-Mail, Phone (there could be others here too, such as BlackBerry PIN, FAX, Mailing Address, Delivery Address,...)

In the first case you want to describe how or why the contact is used and in the second case you want to describe what medium is used to make contact.

These should be considered separately because they are going to drive different application functions. For example, you might have some GUI code that presents different fields and enforce different edits depending on whether you are capturing a mailing address versus a postal address versus a phone number, etc. Similarly, you might have business rules that say Office contact is mandatory and Home contact is optional.

Here is a quick sketch of schema that allows you to make these categorizations:

The CUSTOMER table (for the sake of illustration) contains the people you want to be able to contact. The CONTACT table contains the actual information needed to contact the customer. You could use a generic design or something like XML data to structure this depending on your needs. The two types of categorizations are table driven as follows:

CONTACT_PURPOSE tells you why the contact would be used. This is your Home/Office/... categorization. This is mostly a lookup table but it might have some rules on it like whether or not the contact is mandatory.

CONTACT_FORMAT tells you the structure of the contact. This is your E-Mail/Phone/Address/... categorization. This will have a lookup table element to it, but it could also contain rules for how the contact data is structured, like how many fields? How big is each field? What is the regex pattern used to validate the contact content and so forth.

---

Contact( ID, FirstName, LastName, ..)
Category( ID, Name)
CategoryNumbers(ContactID,CategoryID,Number)

You would need a default category since in this design you cannot add a number without a category.

---

Good explanation by @Joel Brown, I would just like to add.

The Content in Contact can be some other table depending on the CONTACT_FORMAT, for example,

1) if the CONTACT_FORMAT is Address, then it can refer to ADDRESS table which will have fields like street, city, state, country etc.

2) if the CONTACT_FORMAT is Phone, then it can refer to PHONE table which will have fields like ask for, good time to call, area code, country code etc.

like so for different CONTACT_FORMAT's which needs specific fields.