Clarification: I don't know what's up with me and thread titles lately. Can't seem to do it right. I don't need help with the actual SQL. I need help with database design.
I need some help coming with a good database design for the following problem:
- Each article in a store has several properties (be it size, color, dimensions, type, etc...)
- Article properties can be integers, floats, text or enums
- Properties aren't known in advance. That is, they can't be part of the DDL, they are actual data. Properties are meant to be created on demand, if required, as new articles make it into the store.
- Properties may not always be reusable. That is, while an article "size" property can be defined as an enum (a shirt can be small, medium, large), another article can have a "size" property defined as an integer (shoes).
This defines the first part of the problem. A good database design that allows me to implement and maintain variable properties.
The second part of the problem extends the former problem by introducing "categories". You can ignore the rest of this post, by providing an answer to the first part only.
Products are supposed to also be categorized. A category simply classifies the product within a hierarchy. So, an article like Rebook Princess Shoes may be classified as "Shoes"->"Female"->"Sneakers", for instance. The same problem exists, though. All categorization and relationships cannot be defined as part of the DDL since they are actual variable data that is bound to change as the store introduces new articles into its stock, removes others or decides to reclassify existing ones.
Tempting solution to the second part:
Once I find a solution to the first part, I'm tempted to look at categories as just another property and design the database that way. That is, I simply incorporate into the first solution a parent_id field and the field that describes a property data type can be left null or be defined as "category". Root records are considered properties, all others are categories, while the root category references itself.