Any tips on how to design a db (mysql) that should store data about multiple types of posts?

Question

OK , so asuming the db should store data about multiple types of posts (simple posts and complex preformated posts with many form fields ) . It's clear that the data for these posts should be stored in distinct tables .

The problem is that all these post tables should have some extra likes/comments tables that link to them . This will mean more and more tables/querying ...

Would you recommend polymorphic associations like in this answer? https://stackoverflow.com/a/2003042/997178 or this image :

But there is another problem with polymorphic assoc , not all posts (entities) can have comments. And if i would have to do a search to find a post knowing only it's objectid (fk from comments table) , i would have to search all posts table to find it ...

Or you would recommend the concrete aproach and have alot more tables and querying ?
http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

P.S. : any tips will be greatly apreciated :)

Answer 1

I found a partial solution ...

I'll use a mix of polymorphic associations/ base parent table model + polymorphic associations/reverse the relationship model , from this slide :

http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql

Answer 2

Not as you depict it. Consider getting a good database book and reading the portion about normal forms. In very rough terms the idea is to imagine the database as a single huge table with lots of null entries and then split the column set carefully to create more tables. Intuition is not needed. For each of several normal forms (BCNF is a reasonable one to focus on), there are well-defined normalization rules that guarantee a good result. When you don't need any nulls to represent the data model, you are done. In some cases you'll refrain from some splits (and leave the nulls) to avoid joins for performance reasons, but this ought to be done only after benchmarking or if you are very experienced with your environment and the data. A well done normal form database is a beautiful thing. Queries are simple and data integrity is easy to enforce.

The Wikipedia page is a reasonable introduction.