table or extra field

[maxmangion]

When you are about to create a database which will store personal data but for different entities for example Customers and Employees do you normally create two tables i.e. tblCustomers and tblEmployees or you create a single table tblContacts and you add an additional field to store the type of contact?

Note that in the above table i meant that both entities will only have similar fields i.e. surname, firstname, address, email, phone etc.

Thank you

 

[dkinley]

If a combined table, I filter on the appropriate look-up (are they Emp or Cust) for the correct list.

But I do both. My decision is based on how many people will be using, the life expectancy and the expected growth of the db. Only reason is for expediency and the speed of the db reports and lookups so the db won't have to search so many records to fetch one type or the other. I've never performed a formal test to see where this breakpoint is just sorta SWAGged it on a gut feeling from the 3 requirements.

-dK

 

[wazz]

i know of web apps where everything is isolated as much as possible (separate tables). the main separation being users and admins; slightly different.

whichever approach you take, you could also end up with an extra table if you store the personal info separately. (1:1 contact to contactprivatedata).

 

[Banana]

To expand on others' excellent solutions, it also happened that I had varying and sometime conflicting requirements for different entities, so I basically created three tables to satisfy what I perceived to be three distinct requirements; a table that stores only demographics & statistics, another table that holds all information for single person but can only hold one type (e.g. one address, one phone number, etc.) and finally a table that holds only personal data, but has several many-side tables for addresses/contacts where we need to have history for the given person.

In all three tables, those can be used for different entities as long they match the general requirement (e.g. how much do we really need to know about the entity).

The multiple-tiered approach violates normalization, I'm sure, but made it far more simpler to manage instead of trying to shoehorning the third table where we didn't really need all those many-side tables or unwanted data.

 

[GaryPanic]

personally (and from a compliance view ) i would seperate out ...
complaince people can be very narrow in their views and if you don't give them a chance then they cannot complain

 

[georgedwilkinson]

My $.02:

Parties go into their own table, regardless of what their role is within the system.

There are role types (a lookup table) and Parties (people and organizations) have roles within the system (PartyRoles table). I worked at one place that had the same person as an employee, a customer, and a vendor. At another I was both a vendor and an employee. Why would you want to store the exact information in 3 places?

I never, ever store addresses in anything but...an Addresses table, though I do change regarding the relationship with parties (1:M or M:M), depending on the stated system requirements (which usually end up being wrong).

 

[maxmangion]

Thank you very much for all the feedback. Actually my original post wasn't related to a particular database i am working on, but i was wondering what approach other developers take in such circumstance.

Once again, thank you.