Three potential db designs

Question

I've been considering three similar database designs, but haven't come up with a very strong for or against regarding any of them.

1. One huge table for content

   content: int id, enum type, int parent_id, varchar title, text body, text data
   

   This would assign each row a type (news, blog, etc), and have fields for common/standard/searchable data, then any non-standard or trivial data is stored as serialized xml in the data field.

2. One table for ids, many tables for content

   ids: int id, enum tableName, int parent_id
   

   This has one large table for ids, then every other table references this id, making it easy to have hierarchical content.

3. A combination of the two above, where a main table stores all common info, but unimportant data is stored in a respective table.

Naturally it's easier to keep data consistent when everything has its own table, but the above ideas make it much easier to force standardization of common fields, and makes it a lot simpler to relate content to eachother (especially with tagging).

Any thoughts or links would be appreciated.

Answer 1

I think the idea of a master lookup table with secondary tables for actual content is the best solution. Drupal has a similar structure to what you describe and it has proven quite flexible.

http://projects.contentment.org/blog/84

Drupal has a main "node" database in a single table and references specialized tables when getting the actual content.

I'm not a fan of the idea of trying to tuck everything into a table as XML. That could prove to be a performance and flexibility dog over time.

Answer 2

Why not just have different tables for each type of content? News, blog, etc. It seems to me that would be the best and easiest to use option.

Answer 3

Different tables for different types of content. If you ever need to add a new content type or additional features to an existing content type you don't have to worry about breaking the world while you do it.