Q:

Database structure based on post type

I've been wondering what the best way to approach having post types with different fields. A post type of a message (title, body) would have different fields to that of a photo (title, url path, description) etc. 
Ideally I would like to have it under one table Posts as it's then easier to aggregate in a view. It could work showing and hiding fields with jQuery but that would leave redundant fields in the db.
Any suggestions on the best way of going about this?
Thanks

A:

I'd recommend

A generic posts table with a post type field. 
A post type table 
Specific tables for individual post types. 

So for the fields that apply to all posts types, they're stored in the generic posts table. 
You can then use the post type id to locate the additional fields, and to retrieve data for those fields based on the post type id. 
So your generic post table would look like
| id | title | author_id | text |
You post type table would be
| id | post_type |  <-- post_type is a string
And let's say you have a "calendar" post type that needs a date field. We'll call it photo post type. The table would be called "calendar_post_fields", and the post type would be "calendar".
| id | post_id | date |

