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:
- A state can be added without its parent country being explicitly added first.
- The string name is visible in all tables for usability
- 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:
- The "countries" table is checked for a "Ruritania" entry.
- If the a "Ruritania" does not exist in the "country name" column of "countries", it is created.
- 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:
- Is what I am describing possible and sensible?
- How would I ask my question in database jargon so that I can find the answer?
- 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.