Contacts Database Model

IntoTheVoid · 5 Apr 2014 at 19:12

Hi,

I am struggling with a data model for a contacts database. There is a requirement for the following:

Contact: First Name, Last Name
Contact Type: Associate, Friend, Business Contact
Communication Method: Email, Landline Phone, Mobile Phone

I have come up with two tables for the model so far:

Contact: PK Contact_ID, First Name, Last Name, Landline, Mobile, Email
Contact Type: PK ContactType_ID, Contact Type

The Contact Type Table would be a lookup table only. I am not happy with this design as there would be the possibility of the contact not having landline, mobile and email. They may only have one, two or all three. There may be empty fields as a result, which I understand is bad practice. Normalisation is quite new to me. Would it be better to have three or more tables and split out the phone and email into a new table?

I would say in regards to relationships that one contact can have many contact types, and one contact can have many communication methods.

Any help appreciated.

IntoTheVoid · 5 Apr 2014 at 19:42

hi thanks for the quick reply. there would be the possibility of more than one contact type per contact. would this mean that I would need Contact_ID as the Foreign Key in the contact type table or the other way round? also there would be a maximum of one landline, mobile number and email address per contact. thanks.

IntoTheVoid · 5 Apr 2014 at 19:59

great, thanks for you time and insight.

IntoTheVoid · 5 Apr 2014 at 20:05

when you say ID Identity (1,1) PK do you mean the the PK in contact and PK in contact type tables have a one to one relationship? and that the contact table has FK of contact type, and contact type table has FK of contact?

IntoTheVoid · 5 Apr 2014 at 20:57

I see, a sort of composite table. Cheers.

IntoTheVoid · 5 Apr 2014 at 21:12

Does this look reasonable: https://www.dropbox.com/s/bfiy9kcwj0scu73/Contacts%20Model.jpg

Not sure if you will be able to view. Hope so.

IntoTheVoid · 5 Apr 2014 at 21:45

Hi, thanks for the diagram. In the contact type field you have shown, would this be a lookup table i.e in my case there would only be three records Friend, Associate and Business Contact. No additional data would be written there?

I was getting a bit mixed up. I originally had a table with Associate in the first row, Friend in the Second and Business Contact in the third. Then when using a combo box on a form it would lookup these values from the contact type table.

IntoTheVoid · 5 Apr 2014 at 21:59

Sorry for the delay, I have amended my diagram:

https://www.dropbox.com/s/bfiy9kcwj0scu73/Contacts%20Model.jpg

I cannot select nvarchar on Visual Paradigm. Cheers you have been a big help.

IntoTheVoid · 5 Apr 2014 at 22:22

Thanks, not my style just the way I have been learning

for some reason I am finding normalisation and databases difficult, in that it is taking quite a while to sink in. I don't mean it should be easy, as relational theory is beyond me at the moment and I know databases is a very deep topic.

IntoTheVoid · 5 Apr 2014 at 22:35

thanks for the advice. I have been coding in VB.Net, PHP, JavaScript and C++ mostly. Not advanced level mainly procedural, just getting into OO at the moment.

IntoTheVoid · 12 Apr 2014 at 21:54

Thanks for the link, looks interesting.