I am trying to design a database for an ecommerce project.
The current models are categories (which have a foreignkey to themselves) and products (which have a fk to a category). What I am trying to implement now is custom properties.
Essentially, I want to be able to dynamically specify the properties for a given category. For example, in the 'monitors' category, I would want to be able to specify that products in this category will have a 'resolution' property. Note that this is something I want to do dynamically - I do NOT want to hardcode in any properties. The obvious solution to this is to use EAV. However...
When a user navigates to a given category, I need to be able to let them specify and filter based on custom properties. In other words, I want to let a user navigate to the monitors category and specify that they only want to see monitors with a 'resolution' = '1080p'. Well, this is where it gets complicated. I need to know, just based on a category, what properties (like 'resolution') are available, AND what possible values they have (like '1080p').
I cannot for the life of me figure out an appropriate model... can anyone point me in the right direction?
NOTE: I am currently using MySQL, but I am definitely open to other options. For example, I know that NoSQL dbs offer the ability to have N user defined files (because you are just storing arbitrary data) so if MySQL or even SQL altogether cannot provide a 1) reasonably elegant and 2) reasonably efficient solution, I am totally willing to consider other technologies.
you may try to create a table with a field for the FK, another field to the custom option name and another field to the custom option value, like that:
id = 1;
category_id = 1; // FK
custom_option = "resolution";
custom_value = "1080p";
id = 2;
category_id = 1;
custom_option = "touch sensitive";
custom_value = "0"; // 1=Yes / 0=No
So when your customer browse the monitor category, the database can return this category special custom options