I try new table structure for our new eccomemrce website like Wordpress tables. But I am not sure whether it's really good idea for performance.
We've just 2 tables for products and other contents (such us pages, attachments, or even orders). Their ordinary fields kept first table (such as ID, title,date), but their special fields kept second table (such as price, quantity, options, features). That means all special fields of a product are in the just one table.
We thought if we use cache cleverly, it doesn't matter.
What do you think?
(Please if you really have good experience, just answer this question)
Table Structure:
contents: (TABLE 1)
CONTENT_ID TITLE DESCRIPTION DATE
content_fields: (TABLE 2)
FIELD_ID CONTENT_ID KEY_NAME VALUE
Example (Get product that has 1 ID and its shipping is FREE):
SELECT * FROM `contents` as c LEFT JOIN `content_fields` as cf ON c.content_id = cf.content_id WHERE c.content_id = 1 AND cf.key_name = 'free_shipping' AND cf.value = 'yes'