0

The product is water purifier, and we are selling packages included support for changing filters.

Serial is a unique number identifying a single produced item and it's only for water purifiers not packages.

My current solution is like this:

order_product table:

id | order_id | product_id | quantity
1    11          2            2    
2    12          103          2

order_product_services table:

order_product_id | serial    | package_order_product_id
1                  654         2
1                  123         2

As I think it's a common problem, what's the best schema for that?

3
  • Can you please explain 1) if serial is a unique serial number identifying a single produced item or if it is a product variant that correspond to several produced item ? 2) if the serial can be linked to the order product table and if it i is for the water cooler, the packages, or both together (and qty 2 is for the coolers or the packages) ? 3) how you make the join between order_product and order_product_services ? 4) how it would look like if a customer would make a later order for additional support packages ?
    – Christophe
    Commented Apr 22, 2019 at 10:40
  • @Christophe Thanks for your attention. 1) serial is a unique serial number identifying a single produced item. We enter it, after order is placed 2) both water purifier and ` packages` have qty, i.e. a customer can buy 2 packages in a order, or 2 water purifier in a order. Only water purifiers have serial. 3) each order_product has-many order_product_services and they are joined with the order_product_id 4) No problem, the customer can make order for additional support packages for it's water purifier.
    – Ali
    Commented Apr 22, 2019 at 11:23
  • Assuming the rest of the database is ok, constraints are set correctly, and modifying orders is not a thing, I see no problem with what you have. Addendum: If modifying orders is a thing, then I could argue this lacks traceability (who did what when and from where), however you could have that and just not showing it here.
    – Theraot
    Commented Apr 22, 2019 at 12:16

1 Answer 1

Reset to default
0

There is a real life asymmetry that is not reflected in your model:

  • a customer may order a purifier and the related package service, not knowing the serial.
  • a customer may order subsequent packaged service for a purifier whose serial must be known
  • a customer may have ordered and own several purifiers but may order less packaged service (because one of the ordered item is broken or is not used, or was give away as gift to a child, etc...) so that only the customer can know the serial for which the order is placed.

For this reason, I’d rather see the serial as the clue for an existing entity : the produced /delivered item. I’d therefore store it separately from the ordered service, for example in a table of delivered items, that would be related one-to-many to the ordered item (you could for example create these lines when the product is shipped).

This approach would allow to order packaged services, and create the relation between the ordered services and the serial/delivered item when it is known.

You may in a similar fashion track delivered services with a one to many relation between an ordered service for a serial and the related individual service packs delivered.

Now if you do not need to track the deliveries, your approach could work as well. It’s just that you combine information that should be independent, so you might end up with several rows for the same serial, making follow up more difficult.

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .