If I have 2 tables in my database, let's say for instance 'buyers' and 'sellers' but I collect a common set of data for both for instance telephone number, email address, address etc. Is it better practice to have one common table for 'contact_details' with a reference to the table and row that the contact details are for, or as they are seperate entities are they better off in seperate tables entirely?
If 'buyers' and 'sellers' share a lot of data, maybe they're actually just 'contacts', with associated contact information. Whether each contact is a buyer or seller would depend how they were used.
For example, you might have both 'sales' and a 'purchases' table. Each would have a 'contact_id' column, that linked to the primary key in the 'contacts' table.
I would suggest three tables: contacts, buyers and sellers. All of the common data are kept in contacts and anything specific to being a buyer and/or a seller would be contained in those tables with some sort of 1:1 relationship. The fact the relationship exists categorizes them as a buyer and/or a seller.
If you never needed a combined list of the two (of course you can union two tables) or the ability to maintain a single contact that may belong two both, you wouldn't have to worry about it.
Impossible to say in general.
The more fields your contact data have, the bigger the risk that maintaining these data requires duplicated code. But the more often you retrieve buyer and seller records, the bigger the risk that the JOIN required by having a separate table becomes a performance bottleneck.
As you see, the decision has both run-time and development time consequences, and the thing to do is to estimate what your typical usage pattern will be. Then you will know whether normalizing your schema is an overall advantage or not. (When in doubt, measure - performance trade-offs are notoriously hard to predict.)
My two cents:
- Contact can be a buyer, a seller, both or just a contact.
- Contact can have multiple phone numbers and addresses.
- Contact table has basic contact attributes.
- Buyer and Seller have attributes that only apply on that role.
- A Contact that is neither a buyer or a seller has no related row in those tables