During my database schema I've an entity that's recognized. The identifier could be used again and therefore there's a 1-to-many relation using the entity. Example: Of your life a nickname. Nicknames aren't unique and may be shared among lots of people. Therefore the schema might seem like:
PERSON id name nickname_id NICKNAME id name
The problem is the fact that when placing a brand new person, I must first query
NICKNAME to ascertain if the nickname is available. Whether it does not then I must produce a row in
NICKNAME. When placing many, this is often slow as each individual insertion produces a query to
I possibly could optimize large insertions beginning with querying Nickname for the nicknames. JPA query language:
SELECT n FROM NICKNAME n WHERE name in ('Krusty', 'Doppy', 'Flash', etc)
After which produce the new nicknames as necessary, then setting nickname_id around the persons.
This reduces the program a little because it needs to temporarily store nicknames in memory. In addition, some databases possess a limit around the parameters from the
IN clause (SQL Server is 2100 approximately) and so i have perform multiple queries.
I am curious how this problem is worked with by others. More particularly, whenever a database is stabilized as well as an entity includes a relationship with another, placing a brand new entity essentially leads to needing to look into the other entity. For big card inserts this is often slow unless of course the procedure is lifted in to the code domain. Can there be someway to auto place the attached table rows?
FYI I am using Hibernate's implementation of JPA
I am unsure if the ORM are designed for this, however in straight SQL you can:
- Produce a table of title/nickname pairs,
- Place INTO NicknameTable Choose Nickname FROM temp WHERE Nickname NOT IN (Choose Nickname FROM NicknameTable)
- Place into primary table understanding the Nickname is available.
Inside your example, you can easily possess a NULLable nickname column withoout another table, unless of course of your life several nickname.
Truthfully? I'd make nickname a varchar column within the Person table, and overlook the Nickname table. Nickname is definitely an attribute of the person, not really a separate entity.
Is a simplified example, as well as your 'identifiers' really do enjoy the entity-associations?
edit: Okay, understood case a man-made example. Now you ask , a high quality one, since it pops up frequently enough.
Standard SQL supports a kind of Place statement by having an optional "
...ON DUPLICATE KEY UPDATE..." clause. Support with this syntax varies by database brand. Should you give a
UNIQUE constraint towards the identifier title within the Nickname table, a replica entry will invoke the
UPDATE area of the clause (you are able to perform a dummy update, rather than altering anything).
CREATE TABLE Nickname ( id SERIAL PRIMARY KEY, name VARCHAR(20) UNIQUE ); INSERT INTO Nickname (name) VALUES ("Bill") ON DUPLICATE KEY UPDATE name = name;
INSERT INTO Person(Name, NicknameID) VALUES(:name, (SELECT id FROM Nickname WHERE Name = :nickname))
When the Place fails since the nickname does not exist, then place the nickname and so the person record.
I am presuming that :title and :nickname identify host variables that contains anyone's title and nickname - and also the that individual.identification column is going to be designated something instantly when it's overlooked in the SQL. Adapt to match your conditions.
If you feel most nicknames will actually be unique, you can simply make an effort to place the nickname without any reason, but disregard the error that happens when the nickname already is available.
Alternatively, possibly a 'MERGE' statement may help? It provides a choice of placing a brand new value or upgrading an existng value. Syntax and suport varies by DB, but possibly more prevalent compared to 'ON DUPLICATE' option.