Question: Computed id from string as database key

Question

Computed id from string as database key

Answers 1
Added at 2016-12-31 16:12
Tags
Question

I have a XML-Interface with entries that i need to parse and then insert into a database. The entries have all the required information but i want to normalize the database and therefore try to generate objects from the base information.

This is an example of the base entry:

<entry url="http://example.com" author="Ex1" title="Title" category="Category1"/>

And i want essentially the following base objects (pseudocode):

class entry:
    entryID; (PK)
    url;
    title;
    authorID; (FK)
    categoryID; (FK)

class author:
    authorID; (PK)
    name;

class category:
    categoryID; (PK)
    name;

This of course poses the problem that i need to insert the category and author entites before i insert the actual entry to not violate the referential integrity constraint, but i don't want to to write the authors and categories to the database just to immediately recover their new keys and write them back to the entries since that would slow down performance significantly.

Is there a recommended method for this kind of problem or should i just compute a hash based on the name of the category and the author?

Answers to

Computed id from string as database key

nr: #1 dodano: 2016-12-31 16:12

You can use a WITH query to first insert data in a table, RETURNING its key and then insert into another table:

WITH author AS (
    INSERT INTO d_author (name)
    values ($1) RETURNING id)
insert to maintable (id)
    select id from author

(example typed by heart, see https://www.postgresql.org/docs/current/static/queries-with.html for full details of WITH queries).

Source Show
◀ Wstecz