I'm trying to design a database schema that allows me to store customer custom built products. As a simplified example:

  • A user can buy a cupboard, draws, boxes, and dividers.
  • A cupboard has 2 tiers.
  • Each tier can house either a draw or two boxes.
  • The bottom tier can use dividers instead.
  • Boxes can also have a divider in them.

A customer can set up a cupboard and buy it. They can also just buy a draw setup, or even just a box setup. My question is, how do I best represent this hierarchy of customised items in a relational database?

My current idea is to have the following tables:

  • User (user_id, name)
  • Item (item_id, name, price)
  • System (system_id, parent_id, item_id, user_id)
  • Content (item_id, system_id, quantity)
  • Transaction (transaction_id, user_id, time)
  • Order (transaction_id, item_id, quantity)

The system table represents cupboards, boxes, or draws that users have customised. The item_id helps differentiate which it is. If a draw is in a cupboard, then the draw's parent_id would match the cupboard's system_id. The content table holds a list of items within the system that are not customisable, such as the dividers and any future products. A transaction would be the final purchase, and the order would hold each item sold.

My worry with this is that the content and system tables could become huge and full of duplicates (e.g. users will probably have a lot of boxes, they will just have different content and parent_id). Is there a better way of modelling such relationships?

Thank you for your help.

  • Google "mysql bill of materials" for some ideas. – Vérace Feb 6 '15 at 17:19
  • Thanks @Vérace for the advice. It looks like BOM is pretty much what I've already come up with; with my system table being my 'bills'. But with a customer's cupboard producing multiple sub-systems (or a multi-level bill), I will end up with thousands of entries in the systems table. – besiix Feb 9 '15 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.