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 jQu开发者_如何学Pythonery but that would leave redundant fields in the db.

Any suggestions on the best way of going about this?

Thanks

I'd recommend

1. A generic posts table with a post type field.
2. A post type table
3. 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 |

First things first. Do you have to ability to add new "types" at runtime? The answer to this question could invalidate otherwise solid solutions.

If the answer to the question is "no", then go with gargantuan's solution. Have a base table for the common fields, and a separate table for the specific fields. The specific tables have a foreign key to the base table. This way avoids dynamic sql, avoids redundant fields, and gives good query performance.

There are other solutions but I would rather not mention them unless the above solution does not fulfill your need.