Inserting rows into a table that is related to another table
In my database schema, I have an object that is identified. The identifier can be reused, and thus there is a one-to-many relationship with the object. Example: a person may have a nickname. Nicknames are not unique and can be shared by many people. Thus, the diagram might look like this:
PERSON
id
name
nickname_id
NICKNAME
id
name
The problem is that when inserting a new person, I must first query NICKNAME
to see if the alias exists. If not, I need to create a string in NICKNAME
. With many people inserting, this can be slow as each user insertion results in a request NICKNAME
.
I could optimize for large inserts by first querying the alias for all aliases. JPA Query Language:
SELECT n FROM NICKNAME n WHERE name in ('Krusty', 'Doppy', 'Flash', etc)
And then create new aliases as needed and then set the nickname_id for faces.
This complicates the software a bit as it temporarily stores aliases in memory. Also, some databases have a limit on offer parameters IN
(SQL Server is 2100 or so), so I am running multiple queries.
I am curious how this issue is being treated by others. More specifically, when a database is normalized and an organization has a relationship with another, inserting a new object basically results in the need to validate the other object. For large inserts, this can be slow unless the operation is canceled in the code domain. Is there a way to automatically insert related table rows?
FYI I am using Hibernate JPA implementation
source to share
I'm not sure if the ORM can handle this, but in straight SQL you could:
- Create a table of name / alias pairs,
- INSERT INTO NicknameTable SELECT Alias FROM temp WHERE Alias NOT IN (SELECT Nickname FROM NicknameTable)
- Insert into the main table, knowing that an alias exists.
In your example, you can just have a NULLable alias column with another table if the person cannot have more than one alias.
source to share
True? I would make the alias a varchar column in the Person table and forget about the Nickname table. An alias is an attribute of a person, not a separate object.
Is this a simplistic example, and your "identifiers" really benefit from the entity relationships?
edit: Okay, you know, this is just an artificial example. This is a good question, because it comes up quite often.
Standard SQL supports the INSERT form with the optional < ...ON DUPLICATE KEY UPDATE...
" clause . Support for this syntax depends on the brand of the database. If you add a constraint UNIQUE
to the name of an identifier in the" Nickname "table, the duplicate record will trigger part of UPDATE
that clause (you can do a bogus update instead of change something).
CREATE TABLE Nickname (
id SERIAL PRIMARY KEY,
name VARCHAR(20) UNIQUE
);
INSERT INTO Nickname (name) VALUES ("Bill")
ON DUPLICATE KEY UPDATE name = name;
source to share
INSERT INTO Person(Name, NicknameID)
VALUES(:name, (SELECT id FROM Nickname WHERE Name = :nickname))
If the INSERT fails because the alias doesn't exist then insert the alias and then the person record.
I am assuming that: name and: nickname identify host variables containing the username and nickname - and that person.id will be assigned a value automatically if not specified in SQL. Adapt according to your circumstances.
If you think that most aliases are truly unique, you can simply try to insert the alias unconditionally, but ignore the error that occurs if the alias already exists.
source to share