table or extra field (1 Viewer)

[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).

 

[Pat Hartman]

I would tend to separate customers and employees but would combine teachers, students, and employees. Although if employees could also be customers, I would consider combining them. For example in a hair salon, it is quite likely that employees would be customers.

If you are developing a complex application that will have lots of different entity types, you might consider an entity table where common data would be stored. You would then have separate tables for each entity type with the specific fields for that type of entity. All relationships throughout the rest of the application would be to the entity table - never to the specific entity type tables.

 

[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.

 

[Pat Hartman]

I recently had to make a change to an existing application to change its structure from a "company with one address and contacts with no addresses" to a "company with multiple addresses and contacts with multiple addresses". What a PITA. It has almost convinced me that no matter what the user says, always make a separate address table and if it is possible for any other entity in the schema to at some point in the future require an address, plan for it by making an entity table which is where the common fields are stored and the ID is assigned. Then hang the address' off that with a junction table that allows the same address to be used as multiple types.

 

[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.