3

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?

5

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.

4

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.

3

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

2
  • Thanks for this, my feelings are that it seems unnecessary to over complicate the query by normalizing the tables; However as I'm still very much in the learning phase, I would like to adhere to best practises where possible and wanted to check whether this sort of denormalization would be frowned upon should I ever build my tables in such a manner - with the answer being 'no it won't' provided I can justify that normalization would have a greater than negligble impact on performance? Feb 18 '14 at 12:00
  • 2
    Normalize for now. You're too early in your learning to worry about optimizing for performance. The db server will do a much better job if you spend your time learning to design entities and relationships according to expectations. There's also the premature optimization lesson. Search for it, it's worth learning early. Feb 18 '14 at 12:23
1

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

enter image description here

1
  • This makes a lot of assumptions about multiples.
    – tymtam
    Oct 23 '16 at 22:36
0

Is it common in your system for the 'buyer' and 'seller' to share an address?

I'd bet it isn't and because of that I would argue not to have a separate table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.