0

What I want is for inserting the string name of a publication in a child table to associate with the PK of the publication in the publications table. For example, let's say I have a table of counties and a table of states within countries. The states table would have a column for country, with a foreign key in the countries table.

The desired outcome is that I have a "states" table that is a child of "countries" where:

  1. A state can be added without its parent country being explicitly added first.
  2. The string name is visible in all tables for usability
  3. I don't store an actual copy of the string for every entry in every table.

So let's say I want to add a state in a country that does not have an entry. I would like to have it so that when I add an object containing the data for a "Ruritania City" row with a value of "Ruritania" for "country", the following will happen:

  1. The "countries" table is checked for a "Ruritania" entry.
  2. If the a "Ruritania" does not exist in the "country name" column of "countries", it is created.
  3. The new PK for the new Ruritania entry in "countries" is put in a foreign key column of the new "Ruritania City" entry in the "states" table. The PK is used to join the country string name column to all rows in "states" that have the FK of Ruritania.

I'm sure that all of this is very standard database stuff, but I am having trouble finding it in documentation. Most database documentation is written in database jargon, and I am having trouble knowing when I have found the jargon I am looking for.

My questions are:

  1. Is what I am describing possible and sensible?
  2. How would I ask my question in database jargon so that I can find the answer?
  3. If you have specific ideas for implementing this goal efficiently, suggestions are welcome. I am using Postgres via SQLAlchemy, but an answer for any platform should help me find what I am looking for.

Edit in response @mustaccio:

I think keep getting thrown off by the terminology. When I read about an option like "back-populate", and I interpret that to mean that the parent table will be filled with data linked from the child. I wouldn't expect it to work like this out of the box, but what does backfill mean if not that?

Even if the DBMS does not implement all of what I described as one function, I expected that this would be a common enough need that there would be a standard way to implement it, or at least a name for that task.

The point is that I don't want the user to have to think about whether there is a Ruritania country entry before they create an entry for Ruritania City. If the user says that Ruritania City is in the nation of Ruritania, the entry for Ruritania in nations will be created in "countries" if it is not there.

I don't particularly care WHERE this logic is implemented as long as the end result is a table that is comprehensible on its own to a human being even if the database is exported somewhere and separated from the logic controlling the creation of entries.

1 Answer 1

1

What you describe is called "application logic"; none of the DBMSes I'm familiar with implement this "out of the box", obviously. I say "obviously", because many applications would not want parent records created automatically because that just leads to the database being filled with junk, so having this as the default database behaviour would be unacceptable.

If your goal is to offload the implementation of application logic to the database (some people might balk at the idea, others might embrace it), you can do that by creating programmatic objects, such as triggers and stored procedures, in the database. In the latter case your application, instead of inserting rows into tables directly, would call the database stored procedure, which would do the rest.

3
  • I have updated the question in response to your answer. TLDR is that I don't expect it to work like that out of the box, I just want to know if there is a standard approach for this functionality.
    – Stonecraft
    Jul 22, 2022 at 22:16
  • 1
    And the second paragraph of my answer shows how that could be done. It's not "standard" in any sense, because there is no standard, but it's common.
    – mustaccio
    Jul 22, 2022 at 23:42
  • Yes, I think "stored procedure" was the term I was looking for.
    – Stonecraft
    Jul 23, 2022 at 0:12

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.