How to automaticaly create entries in a parent table before filling child table? What is that called in db terminology?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply