CODEWITHSUNDEEP
sqldatabasepostgresql
salmanak
salmanak

Reputation: 5

How should I design a table where a row can have different columns depending on the type of row?

I'm planning to use the Reddit API and store my saved posts in a database. The saves can be of two types - Comments or Posts, both of them have few common columns - author, score, subreddit etc. and a few columns unique to each category:

comment - body_text, comment_id, parent_id, etc.

posts - selftext,link_url,is_video, etc.

I decided to separate the 2 categories into their own tables - Comments table and Posts table. But I don't know how to link these tables to the master table "saves".

My current solution is to have a column kind for the type of save. The comment_id and post_id link the save to its own table. However, this feels like a messy solution and a bit cumbersome. A save can either have a comment_id or a link_id (but not both or neither), and I also have to manage this constraint.

Saves Table : 

+----+---------+-------+---------------------------------------------+---------+------------+---------+
| ID |  Kind   | title |                  post_url                   | author  | comment_id | post_id |
+----+---------+-------+---------------------------------------------+---------+------------+---------+
|  1 | comment | abc   | https://redd.i/redditpostid/redditcommentid | FusionX | 1          | NULL    |
|  2 | post    | xyz   | https://redd.i/redditpostid                 | XnoisuF | NULL       | 1       |
+----+---------+-------+---------------------------------------------+---------+------------+---------+

Post Table :

+----+---------+-------------------------------------------+-----------------------+--------------+--------------+
| ID | is_self |                 selftext                  |       post_url        | num_comments |  thumbnail   |
+----+---------+-------------------------------------------+-----------------------+--------------+--------------+
|  1 | no      | NULL                                      | i.imgur.com/xyz.jpg   |         1020 | someimageurl |
|  2 | yes     | "some random selftext of variable length" | redd.it/redditpostid/ |           10 |              |
+----+---------+-------------------------------------------+-----------------------+--------------+--------------+

Comment table:

+----+---------------------------------+---------------------+--------------------+
| ID |            body_html            |  reddit_comment_id  |  reddit_parent_id  |
+----+---------------------------------+---------------------+--------------------+
|  1 | comment text of variable length | <reddit comment id> | <reddit parent id> |
+----+---------------------------------+---------------------+--------------------+

(reddit ID's are different from my table's own IDs and are only relevant at reddit's end)

Is there a better way to design this database?

Upvotes: 0

Views: 108

Answers (1)

verhie
verhie

Reputation: 1318

I think you should move the owning side of the relation to the two other tables. So instead of having comment_id and post_id columns in saves table, have a saves_id column in post table and comment table.

Upvotes: 1

Related Questions