How to add "Upgrades" entity to product-order database design?

Question

I'm working on a database design for an e-commerce web application. For this question I am simplifying the design and only showing the parts that are relevant to my question.

My things of interest (entities) in this scenario are Products, Upgrades, and Orders.

The Products and Orders are related in a typical fashion for this type of database design, with an OrderItems table to create the many-to-many relationship:

Products
--------
ProductID (pk)
Name
Description
Price

Orders
------
OrderID (pk)
DateOrderPlaced

OrderItems
----------
OrderID (fk, cpk)
ProductID (fk, cpk)
Quantity

Now I'm wondering how to add Upgrades into the mix:

• an Upgrade can be valid for one or more Products
• an OrderItem can involve zero or more valid Upgrades

For instance, say I have these records in OrderItems making up Order #1:

OrderID    ProductID
-------    ---------
1          1
1          2
1          3

And I want to apply Upgrade #1 to Product #1 and #2...

I can imagine adding an Upgrades table and an OrderItemUpgrades table, which will allow me to apply any number of Upgrades to a given OrderItem (line in an Order).

Upgrades
--------
UpgradeID (pk)
Name
Description
Price

OrderItemUpgrades
-----------------
OrderID (fk, cpk)
ProductID (fk, cpk)
UpgradeID (fk, cpk)

I think of this as modeling the "application" of an Upgrade to a Product in an Order.

My question is, where do I model the "validity" of a given Upgrade for a given Product? Should I add a ValidProductUpgrades table that relates Upgrades and Products (or another database layer solution)? Or does that business logic belong at the application layer?

Answer 1

I would do exactly what you have shown in your final code block above. In this case it makes sense to add a separate table that will actually connect the upgrade to a specific order while keeping the product in mind.

Because upgrades can be valid for one OR more products, and with that in mind there could be more than one upgrade applied to a single product; it also makes sense to have the ValidProductUpgrades table, which could then be queried to make sure upgrades for a product are displayed only if they are able to be applied to said product.

I think your assumption above is complete, and is probably the best way to handle this!

Hope this helps!