Page 1 of 1

Database Structure

Posted: 17 Jan 2010, 17:21
by Mclaren
I am in two minds.

In most companies there will be three types of contact.


in a database what is best best practice:

each type of contact its own table, or all contact in a single table with fields containing check boxes to denote which type of contact a contact belongs to ?

In certain circumstances a contact maybe a vendor and a customer or a customer and an employee. my thinking is to combine all types of contacts to one table as this will eliminate the need for duplicate data.

Re: Database Structure

Posted: 17 Jan 2010, 20:47
by Mykhal
I would recommend keeping personal details in a table (Name, address, telephone, email and relation to company!

It would still probably be best to have three tables, as you would keeps different types of information for personel, venors and clients. Also some of your vendors may also be clients! I normally tries to keep as little as possible information about each record in a table. In your case, your employee/customer/creditor number will be used as a key to look up the relevant information in a "Contacts" table.

But hey - this is just how I would do it!

*edit - just re-read your post and saw that this is what you said in your last sentence! so yes, as soon as there is a change of duplicating information move THAT information in a seperate "lookup" table.

Re: Database Structure

Posted: 17 Jan 2010, 21:57
by RuadRauFlessa
Yeah. Put the data that is the same across all three entities in one table and then normalize the rest. So you would have 4 tables. One for contacts which would contain the data which is shared between the rest. One for customers that has customer specific data but references the contacts table. One for vendors and so forth. I think you get the idea.

Re: Database Structure

Posted: 18 Jan 2010, 19:42
by Mclaren
Thanx guys, this is what i was thinking too.