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.

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.
    – Sahap Asci
    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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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