Single-Table vs. Multi-Table design

database-designschema

I'm trying to come up with a database schema which is going to be used for a social network similar to that of Facebook.
Now for posts I have two schemas in mind which are as follows:

  1. Single-Table Design
    In this approach there will only be a singular table for all post types (text, photo, video, etc.).

The structure could be similar to the following: 

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

The content column will contain the json_encoded data for all media types.

  1. Multi-Table Design
    This approach would consider different tables for each media type, which will be one main table that contains the main post record and each media oriented table will contains respective media data.

Posts table: 

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

Photos Table:

|------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  thumb_url  | ...  |
|------------------------------------------------------------|

Videos table:

|-----------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  screenshot_url  | ...  |
|-----------------------------------------------------------------|

and so on…

I would be delighted if you could guide me of which approach is better or any other schema that would serve the purpose in better condition, performance wise.

Best Answer

As with all database design don't think just about what you want to store but also about how you wish to retrieve and modify the information.

If you never need to refer to or search by individual parts of the contents then your first schema is fine. Similarly if you do need to do those things but you are suing a database that allows efficient indexing and manipulation of JSON blocks.

As a general rule though I would normalise the data which means breaking it down as you have in separate tables rather than using one column to store many values.

BTW: the pattern you seem to be heading towards in your second example is called table inheritance - you have a general "posts" object which contains properties common to all your object types and a detail table for each object containing properties that are unique to that object (or at least are not common to other object types). Unless one post could contain multiple photos (or videos, or ...) which is implied by your first design then you don't need a separate ID for each detail table: there will be zero or one rows per row in posts so the post_id is valid as both the primary key and foreign key. Also there is no need to repeat user_id in each table (as that can be derived from the link to posts) unless a different user could add the photo/video to someone else's existing post.

Related Question