How can I arrange contact methods (or similar typed data with different fields)?

Question

I have addressed this issue in, oh, every application I have ever written. I would like a final consensus answer!

Is this the most normalized / efficient / correct way to store contact methods or any other data for this template in the database?

Contact { PK ContactId, ... }

ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Comments }

AddressContactMethod {PK FK ContactMethodId, Line1, Line2, City, State, Zip }
EmailContactMethod {PK FK ContactMethodId, EmailAddress }
InstantMessengerContactMethod {PK FK ContactMethodId, IMClient, IMAddress }
PhoneContactMethod {PK FK ContactMethodId, ... }
WebsiteContactMethod {PK FK ContactMethodId, ... }
OtherContactMethod {PK FK ContactMethodId, ... }

Should I consider the Xml field for ContactMethodDatain the table ContactContactMethod?

Something is just not good for AddressContactMethod, EmailContactMethod, etc., all use the same primary key uniqueness.

I also thought about a key pair of values ​​for contact data, but it will be even more painful than the request than the Xml field.

( : , , ", , .." , )

Answer 1

, , . , , Key * Method - , "" , .

, , . , , , ( , , - ..) . . -, , . , , .

- :

Contact { PK ContactId, ... }
ContactType { PK ContactTypeId, ContactType }
ContactMethod {PK FK ContactId, PK FK ContactTypeId, PK Key, Value, Comments }

ContactMethod.Value - .

, Google . , , , .

Answer 2

. " ".

Contact Methods - .

- " " , . , , " ".

Answer 3

- - :

Person [ID, Name]
ContactPoint [ID, PersonID, ContactMethod]
EmailContactPoint [ID, EmailAddress]
AddressContactPoint [ID, Line1, Line2, blah]

1-1 ContactPoint - . ORM, Hibernate/NHibernate Entity Framework, 1-1.

, , XML SQL, / (, "GetPrimaryPhoneNumber (personID)".

Answer 4

, - , . ; , . , , - , , .

Answer 5

. , ? 1; 2.

, , , , .

, , . 2 , CONTACT HomePhone, WorkPhone . - , , . . .

The reality is that most user interfaces do not show phone numbers in the grid. Instead, they are placed directly according to the person’s name. The reason is that you only need a couple of numbers.

To summarize: keep the database as simple as it can be. The UI team and your database server will thank you.