Good or Bad Idea for CMS Structure?

Question

So, I was going to make a CMS and I just wanted some options.

The idea is simple but no one else seems to do be doing it so there has to be something wrong with the idea right?

Basically, I will have a table called 'fields'. This table will contain individual data fields. I would then have a second table called data_node which is a grouping of fields to create a data object.

So, a data_node would be a blog post. This data_node would have 4 fields. Title, content, created, published.

The fields table would have 4 entries, the data_node table would have 1 entry

At the PHP level, you would then have modules which would access the data nodes.

Is there a downside to this? There would be a lot of work for one table to do but for medium websites this wouldn't be a problem right?

Answer 1

I had developed a system using this architecture some years ago for a scientific project funded by the EU. It was a nice experiment.

The downsides, from my experience:

• You will have to forget referential integrity with the conventional sql methods. No foreign keys, no 'on delete cascade' etc.
• You will do a lot of meta-programming. This is a nice challenge, but it will suddenly become very difficult to correct the bugs until you get used to it.
• Not all the fields have the same data type. Either your table 'fields' will need more columns or you will have to convert everything to VARCHARs and back.

Answer 2

What you have defined is essentially an EAV structure. EAV is typically implemented with three tables, one defining the entities (posts, in your case), one defining the attributes (Title, Content, Created, Published, etc.), and one that provides a single value for a given entity and given attribute.

As you've obviously deduced, EAV structures allow for very flexible storage of homogeneous data, as you're abstracting away everything that defines what something "is" into another layer of data. The upside is the flexibility, but the downsides are many:

• You can't enforce referential integrity (there's no way to define at the physical level that a particular type of entity must contain values for some fields, including relationships between entities)
• Storage is inefficient, as you have to design for the least-common-denominator. In other words, if you're storing the content of a blog post, then every value you store would have to be stored in a very large character field. Non-character data (like your dates or numbers) would have to be converted to and from strings when using them in the application
• Joins become tedious to write. While this is true of 6NF database designs as well and isn't an insurmountable hurdle, it's worth noting. For any field that would need to be transformed into a column in the result set (in other words, if you only want to bring back a single row for a post, with the fields you've defined as columns instead of bringing back multiple rows for a post, with the fields you've defined as rows) requires its own join. This is (or should not be, depending on your RDBMS...if you're using PHP I'm assuming you're using MySQL, and I can't speak intelligently to how it handles many joins) not an issue for the database, but it makes it a pain to write your queries.

EAV structures have their place, though that's usually when developing systems where individual end-users want to define custom attributes for an entity without having to have the application or the database changed. Unless you need this flexibility, the cost greater than the benefit.