Hello, Im trying to design a database to track products and the the monthly orders and revenue brought in by those products. I have a 2 different table designs and am wondering which one would be better: 1) Product Table -------------- PK-ProductID ProductName ProductManufacturer Data Table --------------- PK-DataID FK-ProductID Date Orders Revenue 2) Product Table -------------- PK-ProductID ProductName ProductManufacturer Orders Table -------------- OrderID FK-ProductID OrderDate Orders Revenue Table -------------- RevenueID FK-ProductID RevenueDate Revenue So #1 seems better to me, but should orders and revenue be separated out just because they are two different things? The orderDate and RevenueDate will always be the same (one date, one order number and one revenue number per month) Or should orders and revenue get their own table? This seems like it would make more complicated queries down the road... Thanks for you insights
I'm not sure that I would call the Revenue Table, Revenue Table. If you allow multiple payments per order, you would want to use the second model and "Revenue" should be more like Transactions or Payments. If you only allow 1 payment per order and have no intention of allowing multiple in the future, option 1 would make more sense.
First, 'Data Table', is in the top 5 worst table names ever--its redundantly undescriptive. Speaking of redundancies, your second example has the Revenue table linked to the Product table as well as the Orders table which itself is linked to the Product table--that doesn't seem right. Could you provide some example data and then talk us through how you envision it working.