Storing Product Properties

Question

I'm creating a jewellery product catalogue application and I need to store properties for each product such as material, finishes, product type etc.

I've concluded that there needs to be a model for each property, mainly because things like material and finishes might have prices and weights and other things associated with them.

Which of the two options will be the most efficient way to store data and be scalable

1. Create a model PropertyMap that will map property types and IDs to a Product ID.
2. Create several other models such as ProductMaterial, ProductFinish etc that will made a property to a product

All the data needs to be searchable & filterable. The database will probably index around 10K products.

Open to other smarter ways to store this data as well!

Answer 1

As a rule of thumb, to get the most out of your database tools, it's best to normalize your data according to the typical SQL conventions. That means that a bunch of fields that have a one-to-one relationship with each other should be collected together into the same table. That way you can grab them all (and they're frequently needed together) with a simple and efficient query.

If you instead have to gather them up from some different organization, both you and the database will end up having to do a lot more work. It will scale poorly, both on the hardware and in your brain as you struggle to maintain and extend it.