> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of James B. Byrne > Sent: Monday, April 06, 2009 1:46 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
