We are designing a system similar in complexity to an ecommerce platform that needs an open schema design.
Our particular problem is that our “products” table/s needs to hold information about different types of products (say Books, DVD’s and SmarthPhones).
Each of these prod types have different attributes (number of pages for books, length in minutes for DVD’s, processor frequency for Smartphones).
We are aware of a few different approaches on the structure of the table/s to accommodate this (like nosql, object-attribute-value in sql, additional tables per product type etc) but since we are considering yii for the job we were wondering what would be ‘the yii way’ to approach this problem.
We would prefer to use SQL and get started on yii1.
Any comments and suggestions would be appreciated.
noSQL is an option, and MongoDB would probably be best suited for ecommerce, but we still feel the architecture is rather relational oriented, and would not give up mysql just yet.
After some additional consideration, it seems that innodb on mysql would be a good option, by using the COMPACT row format, and think about some sort of dynamic model regeneration.
I did something similar in PostgreSQL as a few simple tables with columns such as ‘type_id’, ‘name’ and ‘value’ and it works pretty well. Those properties are only displayed on the product detail page and not really indexed or searched through. Adding that shouldn’t be a problem. It would be hard enough to create some strategy to pick certain attributes and weights for them, the storage is not an issue here.
If you need to regenerate models ‘dynamically’ that indicates your DB schema design is very flawed, you’re abusing SQL and either should fix that or switch to some noSQL.
I put together a system with fairly complex requirements including nested product categories (several of which could be assigned to the same product) and custom attributes, which could be assigned to groups and made filterable and sortable, or assigned to products for display only on the product page. The tables I ended up creating were:
