Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
There are dozens of open source content management systems out there. Why do you want to write another one? –  Mike Sherrill 'Cat Recall' Mar 1 '11 at 2:34
    
The CMS in my question is actually just an example to have an easy to understand situation. –  Damien Mar 1 '11 at 8:58

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
I edited my question to take into account the fact that an author is just a regular user, since I don't quite get the "user_author_*" tables you suggest. There is no table "author" in my mind. Your last sentence anyway makes me believe my idea might actually work... –  Damien Mar 3 '11 at 14:27
    
@Damien, I added some explanations to my response. I hope it helps. Given the new information you appear to need two tables for users and content, plus one or two relationship tables to join them. You will appear to have a one to many relationship from users to content, in addition to any many-to-many relationships you will have. –  BillThor Mar 3 '11 at 16:56
    
It all makes sense now, thanks for the lengthy edit! –  Damien Mar 3 '11 at 17:01

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.

share|improve this answer
    
I was tempted to look into the NoSQL world, but it seems like there is no good native support for it, especially considering the fact that I'd like to stick to the CakePHP framework. I'm still pondering between InnoDB / MyISAM though. –  Damien Mar 3 '11 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.