I have the following PostgreSQL tables structure:
CREATE TABLE parent ( pk serial CONSTRAINT parent_pk PRIMARY KEY, name VARCHAR NOT NULL ); CREATE UNIQUE INDEX parent_name_uindex ON PARENT (name); CREATE UNIQUE INDEX parent_pk_uindex ON parent (pk); CREATE TABLE child ( pk serial CONSTRAINT child_pk PRIMARY KEY, name VARCHAR(512) NOT NULL, ); CREATE UNIQUE INDEX child_pk_uindex ON child (pk); CREATE UNIQUE INDEX child_name_uindex ON child (name); CREATE TABLE parent_child ( child_id serial REFERENCES child (pk), parent_id serial REFERENCES parent (pk), CONSTRAINT parent_child_pkey PRIMARY KEY (child_id, parent_id) );
In my code, I get a hashmap with all parents as a key and its children as a value. One child may have multiple parents. I want to create the associations in
parent_child table, but I only have the parent and children names.
I'd like to efficiently put them into the database. The most obvious way to achieve it would be to fetch the ID's of every parent and child and perform a query that would create
parent_child instance by these IDs. But I think if it wouldn't be better to use these ID's and some kind of nested query to resolve the ID from the name. I think it should use less memory from the application, and the database should be pretty efficient by it, but I'm not sure if for large datasets where one child is used very often it wouldn't make redundant operations.
Let's assume the following structure:
parent1: child1 child2 parent2: child1 child3 parent3: child1
I was thinking about making the following query:
INSERT INTO parent_child (parent_id, child_id) VALUES ((SELECT pk FROM parent where name = 'parent1'), (SELECT pk FROM child where name = 'child1')), ((SELECT pk FROM parent where name = 'parent1'), (SELECT pk FROM child where name = 'child2')), ((SELECT pk FROM parent where name = 'parent2'), (SELECT pk FROM child where name = 'child1')), ((SELECT pk FROM parent where name = 'parent2'), (SELECT pk FROM child where name = 'child3')), ((SELECT pk FROM parent where name = 'parent3'), (SELECT pk FROM child where name = 'child1'));
SELECT pk FROM child where name = 'child1' is repeated here 3 times. Would the database use a cached result or execute the query every time? In reality, there will be thousands of records and thousands of such repeated queries.
child may not exist at the time of making the association. Would it be possible to create one if It doesn't already exist? Would that be efficient?
What would be the most efficient approach to do that?