mysql content fields idea :)

Question

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'

Answer 1

I have really bad experience with this model, do I qualify? ;)

This kind of abstraction can make sense, but up to a point - you have to weigh the similarities and differences between various objects: for example, grouping CMS templates and e-mail templates together makes sense (as many important parts would be shared), grouping CMS pages and delivery orders doesn't make much sense (as they only have a few, relatively unimportant, shared pieces of data).

The decision "what is similar enough", will be subjective; but it needs to be done. Otherwise, you may fall into the trap of "everything is an object".

True Story: one company I've worked with had a similar idea: everything (a CMS page, an order, a customer, an e-mail) is an Entity and is stored in the entities table; properties specific to the object go to entity_properties and entity_property_values tables. It was a Huge Unholy Mess (TM), as the objects had almost nothing in common, so 99% of the data was stored as entity_properties and/or various references to other Entities. (In other words, abstracting everything to a common base object has left us with many useless base objects).

Answer 2

This is a bad idea: don't do it!

The database tables, and the relations between them, should model your data.

The simple model you suggest prevents you from indexing your database effectively, and makes querying the data extraordinarily difficult. (The model you suggest is sometimes necessary, but only when you do not know the format of your data.)

Are you suggesting this model because you hate having lots and lots of tables? I agree that lots of tables can be confusing, but a good naming convention should sort this out. For example, in one of my databases

• all tables have a prefix of a letter then an underscore,
• all tables to do with customers have prefix c_, for example c_customer and c_address,
• all tables to do with products have prefix p_, for example p_product, and p_price_list.