We will be creating a MySQL database to reflect the following PHP classes:
There will be models associated with Content, like Comment, Ignore etc..
The question here is which data model to use.
1) One table per model
2) One large content table (with all class properties as columns), plus single tables for the other models. I believe it's called single table inheritance.
3) A large content table with the common fields, or only the fields we commonly search on (indexes), plus a large content-data table, with the rest of the properties.
4) Another way?
We want to be able to query against the Content, and get recent, location-based or somehow filtered content, regardless of which content type (subclass) it is. Therefore solution 1 is probably out of the question.
This system should be quick and scalable. What is the best way to go about this problem?
I wrote a blog post about this topic that you may find helpful: http://jasonswett.net/blog/composition-a-better-alternative-to-orm-inheritance/
First, I'd like to recommend that you call your table content_item or something instead of content. The reason for this is I believe the table name should match the name of the thing it represents. See this other blog post of mine for details on that.
So you might have a table called content_item, a table called article and a table called post. content_item would have columns like title, slug, etc - everything that every content item will have, whether it be a post, article or something else. Then article would have a content_item_id and then anything else that's specific to articles, but only what's specific to articles. For each article, you'd have a content_item record, then an article record that's attached to that content_item record.
This would give you the ability to query content_item and get results that include all content types.
I've implemented this kind of solution in the past with good success. (I've also tried different approaches and didn't like them as much.)