I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields for all platforms, even the platform specific ones. So a saved Facebook post might contain an empty column called retweets(Twitter specific).
Another solution is to create a Supertype post which will contain all the general information plus the type(facebook, twitter) and have a relationship with all the platforms specific tables.
A third option is a general and platform specific table with no relationship with each other, Posts contains all the general data and the platforms specific(facebook_posts) table contains the general and the platform specific data they have no relationship with each other only with the user.
What do you think is the optimal option for performance and future proofing? We might want to add more platforms and maybe remove\changes exciting ones. The database might contain 10s of thousands of posts at some point in the future.
Option 1: All information in one table
Table name: Posts id title facebook_id retweets
Option 2: Supertype with multiple relationships
Table name: SuperPosts id type title Table name: facebook_posts id facebook_id shares Table name: twitter_posts id twitter_id retweets
Option 3: General and platform specific no relationship with each other
Table name: Posts id platform_id text Table name: facebook_posts id facebook_id facebook_text shares Table name: twitter_posts id twitter_id twitter_text retweets