0

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?:

  1. Create a supertable with a UUID as the primary key
  2. Create several subtables where each have as their primary key the primary key from the supertable as a foreign key.
  3. 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') returning id ) insert into videos (guid) select id from new_post;

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.

Improve this question
6
  • You are looking for triggers. Commented Jul 22, 2023 at 20:44
  • Thanks! A friend just said the same thing to me. Have to say though, I feel pretty clueless, not sure it's even possible to do them via the ORM. Any examples of triggers that do this sort of thing (whether ORM or plain SQL) would be super helpful. Commented Jul 22, 2023 at 20:53
  • Postgresql's inheritance feature really fits with the issue. Commented Jul 22, 2023 at 21:10
  • @SahapAsci yes, that also look very useful. I think I have a bit of reading to do before I know whether to use inheritance, triggers, or both. Commented Jul 22, 2023 at 22:18
  • Although I did try some stuff with inheritance using this part of the SQLALchemy documentation, but perhaps I didn't try the right example. Everything I did, I still needed to create the parent row first. I suppose this is where triggers come in. Commented Jul 22, 2023 at 22:21

1 Answer 1

Reset to default
0

I'm going to suggest several lines of research to you. Most of what you find is going to confirm the three design choices you highlight in the question.

If you look up "class table inheritance", you will get numerous papers that cover table design for superclass/subclass situations. My favorite write up is Martin Fowler's.

If you look up "specialization/generalization", you will get several writeups that address how the ER model was extended to cover the superclass/subclass case. The ER model doesn't help you with design issues, because the ER model is intended to be design agnostic. Many people come up with a relational data model, then depict it with an ER diagram. That's misleading in this context.

And, of course, the object model has inheritance.

If you lookup "shared primary key" , you will get some writeups that cover using a foreign key as a primary key.

Your best bet is probably to use Postgress specific features intended to address superclass/subclass cases, provided you can get around the limitations of your ORM tools. Other answers have addressed this approach.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.