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.

davetherave2 · 5 Apr 2014 at 19:35

Personally I would stick with having the fields as they are. There is a point at which normalisation can get in the way of readability in a database and then you are constantly having to reference multiple tables/construct views to make the system easy to read. If the person could have multiple emails or phone numbers then fine extract them out and link to the contact table. But in a small table structure like this I don't see any adverse impacts. It looks like from the two table you are missing a table/field to link the contact to the contact type table. This may be missing intentionally. Again if the contact can only be linked to one contact type then including a foreign key into the contact table will be fine. I try to remember KISS (keep it simple stupid) when embarking on a new project and this may mean refactoring tables/classes to smaller components but ensuring I am keeping logical items together.
Hope this helps.

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.

davetherave2 · 5 Apr 2014 at 19:56

If the contact can have multiple types then you would have a third table that links the contact Id with the contact type id.

So for example
CONTACT TO CONTACT TYPE TABLE
ID IDENTITY(1,1) PK
CONTACT ID FK
CONTACT TYPE ID FK

You could get away with not having the id on this new table but I personally prefer having an id on composite tables. If there is a maximum of one phone, mobile, email then I would keep it in the contact table. If more contact methods come in then you could extract them out. If you anticipate that more methods will be required in the future e.g. fax, Twitter, Facebook etc then I would extract them out now and have a similar linking table structure like the contact type table.

There is always the danger to over engineer a solution so as I said keep it simple and then refactor when required.

IntoTheVoid · 5 Apr 2014 at 19:59

great, thanks for you time and insight.

davetherave2 · 5 Apr 2014 at 20:03

Not a problem.

I'm not saying that this is the only solution and I am sure others may suggest something different but this is what I probably would do as a first iteration of a database design.

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?

davetherave2 · 5 Apr 2014 at 20:41

Sorry identity(1,1) is just an auto incrementing value that I have identified as the primary key by putting the PK after it.

It's just my shorthand for showing its auto increment although it is is used in t-SQL to do auto incrementing.

davetherave2 · 5 Apr 2014 at 20:48

Eisenhorn said:
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?

So if you had the following contact ids in your contact table

1. CONTACT 1
20. CONTACT 2
100. CONTACT 3
345. CONTACT 4

Then you had the following contact type ids

30. CONTACT TYPE 1
40. CONTACT TYPE 2
50. CONTACT TYPE 3

Then in the new table I generated you would have for example

ID. CONTACT ID. CONTACT TYPE ID.
1. 1. 30.
2. 1. 40.
3. 100. 50.

Hope that helps visualise it.

IntoTheVoid · 5 Apr 2014 at 20:57

I see, a sort of composite table. Cheers.

davetherave2 · 5 Apr 2014 at 21:00

Yes. Which is why I said you could do it without the additional id (which I think is probably the best practice way) but I personally prefer having the additional id field on there.

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.

davetherave2 · 5 Apr 2014 at 21:32

Ok. I am confused with your contact type table as you have listed the types "associate", "friend" as fields and not rows of data in the system.

this is probably what I would do as a starting point:

You may notice that I have changed your char fields to nvarchar purely to handle foreign characters, I have also upped your email field to 128 characters (I think this is the standard max size for emails but you could increase this if you need to.)

I have also changed your phone fields to nvarchar fields purely so that it handles things like "-" or "(" or ext numbers being added in.

I added an enabled bit field to the contact types as well just in case you decide to disable a contact type in the future.

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.

davetherave2 · 5 Apr 2014 at 21:50

Eisenhorn said:
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.

Yes. that is correct. this is being treated as a look up table for you.

If you needed to hold more information then you could do that here or that information could be stored in the composite table if it is appropriate.

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.

davetherave2 · 5 Apr 2014 at 22:10

My diagram is from using Microsoft SQL Server 2012 rather than any modelling tool.

Glad I could help.

Personally I would get rid of the underscores in your first name/last name fields (again this is a personal thing here) and I would probably take out the prefixes of ContactType/Contact from your id fields as you know these are the id's for the respective tables (which again you are prefixing with tbl (again not a personal fan of this type of prefixing)). (Again this is down to my personal preference here) but other than that it looks ok to me.

But if this is your coding style and you understand it then keep with it (as long as you are consistent then continue with what you are doing)

Note: (The minor issues I have highlighted are just irritations to me (I don't know why they are, they just are

) . I probably have some coding habits which other developers would probably critique as well which are annoying/irritating. )

Glad my advice helped and if you need any more assistance feel free to ask.

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.

davetherave2 · 5 Apr 2014 at 22:31

If you can understand the basics of object orientated design in another language say c#, java etc then applying those rules to a database should be easy. just think of tables as classes and columns as properties.

As for normalizing a database this can be done in a number of ways but comes with experience and some improvements will be obvious others will be more subtle. Personally I go with the general rule if I am repeating the same bit of information over and over again in a table or number of tables (say of example title or job title) then this can be normalised and referenced via a lookup table.

If you haven't done it already then try either http://www.microsoftvirtualacademy.com/training-courses/software-development-fundamentals

or maybe look at setting up a trial account with say www.pluralsight.com as there are a number of great courses on offer.

Alternatively and I did this when I first started out use one of the "dummies" books as this is a great starting place and doesn't assume you have any knowledge and can be a great reference source for database design.

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.