Database Structure

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Database Structure

Post by Mclaren »

I am in two minds.

In most companies there will be three types of contact.

Employees
Vendors
Customers

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.
Mykhal
Registered User
Posts: 2166
Joined: 14 May 2009, 19:56
Location: Here

Re: Database Structure

Post 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.
Image
Dual Core E2140@1.6GHz, 2 Gig Ram, 500 Gig Samsung HD, 80 Gig Maxtor, Geforce 7300se :oops:
Thanx Ama :notworthy:
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Database Structure

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Database Structure

Post by Mclaren »

Thanx guys, this is what i was thinking too.
Post Reply