How wrong is my web app modeling schema?

Question

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years now, but never had any theoretical education, so I'm totally ignorant about database best practices, and would really like to learn more.

The problem is as follows: the application is going to be a very basic CMS that allows to store blog posts, events, photo-galleries, and other various items. Now all those items share the same attributes (more or less, but let's say they do) which are user_id, title, date, content. Furthermore, they all can be "commented", "favorited", "shared", etc. And finally, they can be linked to each other (a blog post can refer to an event, which has pictures, etc.). Because of that, I am tempted to just create one table called "items" and have a "type" field to differentiate the items. The consequence would be to have only one "join" table for each action: "users_comments", "users_favorites", etc. and a join table for item connections between each other. Ultimately, there could be more item types in the future...

I kind of feel like this is a bad lazy way to go, mostly because of performance, but I really would like to have your opinion on this. Does the lock effect will render the site totally useless as multiple people will try to read and write those "items"? For what it's worth, I'm using MySQL with MyIsam, and CakePHP.

Beyond this question is the fact that I'm not used to large scale applications and always wonder about the performance. I just read about 3NF and am going to keep on learning those things, so any reading hints would be greatly appreciated.

Answer 1

Seriously look at third normal form. I would use surrogate keys with the natural keys implemented as unique keys. You will likely find that author belongs in its own authors table. You may find you have a few tables which are quite similar such as user_content_faves, user_author_faves, user_author_shares. This is normal.

Having a single content table with a content_type column may be appropriate. The content column would need to be capable of storing all the content types.

EDIT: For relationship tables I usually name the join table by concatenating the names of the joined tables, abbreviating as necessary. If there multiple relationships between the two tables, I use one of two options:

• Append the purpose of the relationship (as I did above); or
• Add a type/reason code to the relationship (in which case the type is not needed in the relationship name).

I made the assumption that you would want to track who favorited or shared things. It appears that both the producer (author or user) as well as the content items. Therefore you have users favoriting a producer (user_author_faves now user_user_faves), or a product (user_content_faves). Depending on how you do sharing,

• it could be an attribute on the content, or
• a relationship like user_content_shares, where content is share with a particular user. Re-sharing could be problematic, if you track who shared things, and multiple users share the same content to the same user. Un-sharing re-shared items is problematic if you don't track who did the sharing.

You may want to consider (and set policies for):

• tracking/audit information like when something was done (added, favorited, shared, etc).
• whether to do physical or logical deletes.
• if you do logical deletes how to handle refavoriing or sharing something after a logical delete.

When indexing relationship tables I generally have the primary key consisting of the primary keys of the two tables being joined. A second index with the primary keys reversed, or just the primary key which is the second column in the primary key is usually required. If the relationship between two rows can occur more than once, the column(s) used to differentiate the reason/type and/or timing (since date) of the relationship needs to added to the primary key.

Answer 2

Creating generic tables with e.g. discriminator columns like you seem to suggest is a possible, but somewhat tedious practice when using an RDBMS - although some ORM frameworks have pretty good support for it. However, it will definitely cause a performance hit. It sounds like you are looking for a hierarchical model rather than a relational model. Maybe a non-relational/document database would better suit your needs, as to provide you with enough flexibility to add "types" in the future.

Also, if you insist on using MySQL for this situation I'd recommend using InnoDB instead of MyISAM. MyISAM has a few disadvantages such as no real foreign key support, table level locking, no crash recovery etc.