Re: INSERT or UPDATE

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of James B. Byrne
> Sent: Monday, April 06, 2009 1:46 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] INSERT or UPDATE
>
> I have spent the last couple of days reading up on SQL, of which I
> know very little, and PL/pgSQl, of which I know less.  I am trying
> to decide how best to approach the following requirement.
>
> Given a legal name and a common name and associated details, we wish
> to insert this information into a table, entities. As well, we
> believe it useful to allow a specific entity more than one common
> name.  So, at the moment we are considering having another table,
> identifiers, that takes entity_id = entity.id (synthetic sequenced
> PK for entities), the identifier_type (always 'AKNA' for this
> collection of identifiers) and identifier_value =
> entity.common_name.
>
> This seems straight forward enough when initially inserting an
> entity.  However, it is conceivable that over the lifetime of the
> system a particular entity might change its common name. For example
> the former "John Tash Enterprises" might become popularly known as
> "JTE Inc." while the legal name remains unchanged.
>
> When we update the entity record and set the common_name = "JTE
> Inc." then we need insert an identifier row to match.  However,
> identifiers for a given entity can be maintained separately from the
> entity itself. It is therefore possible, indeed likely, that the
> identifier "JTE Inc." for that entity already exists.  Likely, but
> not certain.  In any case, the old identifier row remains unchanged
> after the new is inserted.
>
> The issue then is how to determine on an UPDATE entities whether it
> is necessary to INSERT a new identifier using values provided from
> the entities row.
>
> From what I have gathered, what one does is simply insert the new
> identifiers row. If there is a primary key conflict then the update
> fails, which the function handles gracefully. If not, then it
> succeeds.
>
> I have also formed the opinion that what one does is write a
> function or functions, such as fn_aknau(entity_id, name), and tie
> these with triggers to the appropriate actions on entities such as:
>
> CREATE TRIGGER tr_entities_aioru AFTER INSERT OR UPDATE
>             ON entities
>            FOR EACH ROW
>        EXECUTE fn_aknai(entities.id, 'AKNA', entities.common_name);
>
> Is my appreciation correct or am I missing the mark entirely?  Is
> this considered the proper place and means to accomplish this sort
> of task in an RDBMS?  Does it belong elsewhere? Am I correct in
> inferring that the values in the columns id and common_name will be
> those of entities AFTER the insert or update and that these will be
> available to the body of the function?  Is the trigger dependent
> upon a SUCCESSFUL INSERT or UPDATE of entities or will it be called
> regardless? Must the function be written in PL/pgSQl (or similar PL)
> or could this function be written in straight SQL? Should it be
> straight SQL if possible?  What should the function return, if
> anything?
>
> Fairly basic stuff I am sure but somewhat mystifying for me at the
> moment.  Any help would be appreciated.

It is a difficult question.

For instance, there are many possibilities when a collision occurs.

I guess that for some collisions, sharing the name is OK.

Consider two different fictional companies (hopefully in different
domains):
Jet Propulsion Industries Incorporated == JPI Inc. (makes jet engines)
Journey Protection Investments Inc. == JPI Inc. (underwrites travel
insurance)
Probably, they don't have a legal battle because they have completely
different domains.
So it seems OK for both companies to relate to this entity if it is only
used as a label.

On the other hand, you may have a typographical error on data entry for
a computer firm.
If you label a company as "IBM" when it should have been "IBN" I guess
that won't make anyone happy.

I think that the real issue is that you must truly and carefully
identify your business rules and model those in the database structure.
Said another way, "How would a human handle this issue given a name
collision?" If the answer is not obvious, then maybe you need to write
an exceptions log and handle each case by hand that is not solved by a
simple and clear to understand rule.