Search code examples
sqlanti-patterns

Database design - Do I have redundancy here?

I have four tables: Companies, Products, Customers and Sales. They are related as follows:

  • One Company can have multiple Products
  • One Company can have multiple Customers
  • A Sale is a child of a Product and a Customer. Each Sale record refers back to one Product and one Customer.

I am feeling uneasy about the fact that you can trace back from the Sale to the Company via two different routes. This raises the potential for data integrity issues - what if the Products route says the ultimate parent is Company A, and the Customer route says it's Company B?

This sounds like a SQL-antipattern to me, but I could be wrong. What can I do about it?

Thanks!

Solution

  • It depends. You say:

    One Company can have multiple Customers

    Does this mean that each Customer only shops at one Company?

    If not, shouldn't the relationship between a Customer and a Company be defined by the Products they have purchased?

    This would mean removing the fk_Company field (or equivalent) on the Customer.

    However, if the relationship is instead the Company that the Customer registered at, this is different.

    In that case, if there was a conflict between the Products bought and the Company the Customer "belongs to", it is not a real conflict - the conflict would be between the Company the Customer registered at, and the Company that the Customer has just shopped at, i.e. not a real conflict.

    The ultimate question is, what is the relationship between a Customer and a Company - is it where the Customer

    • Last shopped (redundant)
    • Usually shops (redundant)
    • First shopped (redundant)
    • Registered (perhaps not redundant)