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 removechanges 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
You are asking how to implement inheritance on a relational database, and the 3 methods you mention are basically the 3 most common/basic ways to do that. See https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database for some discussion about them.
There is no “best” way to do it- it will all depend on how you intend to use your schema so it facilites your queries afterwards.
For miscellany columns, especially those that are optional, toss them into a
JSON column. Do not plan on filtering or sorting on anything in the Json column.