1

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
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.