Sorry about the title, I couldn't find anything better. Suggestions are welcome.
Say we have two tables,
The same product can come from many different suppliers, so we create a third table, let's call it
SuppliersAndProducts, with this schema
SuppliersAndProducts - id (autoincrement) - supplier_id - product_id - from_date - to_date
to_date are there to account for the fact that a given supplier might well stop selling a given product, but could also begin selling it again at some point in the future.
Now, we want to store the price we pay when we buy a
Product from a given
Supplier. Prices, of course, change over time, and we want to keep a track of that too.
So we introduce a table, let's call it
SuppliersAndPrices, structured like so:
SuppliersAndPrices - id (autoincrement) - supplier_id (FK to the supplier id) - product_id (FK to the product id) - price - from_date - to_date
My question about this last table is more conceptual than anything else: should this table be like I described (meaning that the association in based on both the supplier and product IDs) or instead just reference the id from
SuppliersAndProducts with a column that we could call
The latter design is more normalized than the former; after all, the association between a
Supplier and a
Product is already stated in
SuppliersAndProducts, so there would be little point in repeating that information. Still, at least to me, for some reason the former feels closer to the real world.
From the point of view of query complexity, to know the price I'm paying today for a given product, with the first design I would have to write (with some pseudo-SQL to remain db-agnostic) something like:
SELECT price FROM SuppliersAndPrices WHERE supplier_id = X AND product_id = Y AND today is between from_date and to_date
whereas the second design would entail a join, thus making the query look like:
SELECT price FROM SuppliersAndPrices INNER JOIN SuppliersAndProducts ON SuppliersAndPrices.suppliers_and_products_id = SuppliersAndProducts.id WHERE SuppliersAndProducts.supplier_id = X AND SuppliersAndProducts.product_id = Y AND today is between SuppliersAndPrices.from_date and SuppliersAndPrices.to_date
I'm writing this off the top of my head, sorry about my SQL.
Also, now that I think about it, with the second design I would have to add another condition to the
WHERE clause to check the dates in
SuppliersAndProducts too, to handle the case in which a
Supplier will stop carrying a product and, at some point, begin selling it again. In that case the join condition would return more than one row, and that would be Not A Good Thing™.
So, which one would you choose? Associating on the id of what already is an association in the name of greater normalization but at the price of increasing query complexity? Denormalizing a tiny bit to make querying easier and, arguably, the database structure clearer for future maintainers?
The query for the second design would probably be hidden behind a view, but that's still code that has to be maintained and understood. What I really want to know is which one of the two design is "better", for some definition of "better".