0

I have 2 options for making a database. Which one could be more future proof and why?

Use-case: We have a shop with different products like 50. They are categorized into 6 different types of packaging (tubes, buckets, glass,...). Now we want to keep the relations between them so we know which product has which packaging so we could calculate the cost. (The cost fields are left behind in the designs). So we are looking to find which way could be the best to keep those relations clear and maintainable.

Option 1: Option 1

Option 2: Option 2

I think the first option is more maintainable than the second one whereas the second one is shorter in table design but you have to foresee each product seperately for their whole package.

So which one is better when the database gets bigger and could be more futureproof?

2
  • Option 1 doesn't really give you flexibility to "change" packaging on a product, without losing any historical information. With Option 2, you could at least have effective / status fields on the "Product Material Type", so you can readily identify a product's "current" packaging, but also maintain an historical reference (if that's something that is of relevance)
    – Craig
    Oct 5, 2023 at 21:53
  • @Craig But if I have historical tables running next to product and packaging isn't that enough? Because in the Product one i will see when the packaging relation is changed (as in i set another packaging to that product) and in the packaging one i can see all the changes made in the items and so on? So it would be probably just a lot more complex to get the historical data but it isn't impossible? Oct 10, 2023 at 15:35

1 Answer 1

0

I'd do it slightly differently. Considering that you can have a 1 Kg Strawberry cake and a 500 gr strawberry cake, I'd have a product_category (Strawberry cake), that would have several products (1Kg, 500gr), and each product would have several product_material_types and packaging would be an attribute of a type. So that you would have a total view of the cost (with packaging), know how much is in each package (just an attribute, easy to separate), and what is needed to have a complete product without having a separate table for the packaging that is essentially just an item/product/material of the warehouse.

1
  • Yes indeed could have been an option but the product table is not changeable in the way that it's an old database that they want to keep 😅 where products already exist in the way strawberry cake 300g and strawberry cake 1kg, strawberry cake 6 x 60g as example 😁 Oct 10, 2023 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.