I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?:
- Create a supertable with a UUID as the primary key
- Create several subtables where each have as their primary key the primary key from the supertable as a foreign key.
- When I add a row to a subtable, a corresponding row is created in the supertable. Probably by automatically creating the supertable row first before creating a new row in the subtable with it as a foreign key.
In my case, I am designing database that will store several types of post (text, video, image). Each (sub)type of post has unique attributes, and thus should get its own table. However, I want all posts to be organized by a GUID in a supertable that contains attributes shared by all post subtypes. I've been able to set up these tables, but I dislike the fact that I cannot enter into a subtable a post of a given subtype and automatically populate the supertable.
It's been suggested that I just create both supertable and subtable entries with one command like this:
with new_post as (
insert into posts (name) values ('My new video post')
insert into videos (guid)
However this seems awkward. I know there are lots of ways to set up relations and backfill columns in other tables, yet I cannot seem to find an example of this particular type of backfilling relationship.
I happen to be using Postgres via SQLAlchemy, but an answer in general terms about how this problem would be approached in any DB would be welcome.