Data Model Advice

[Gary Chambers <gwchamb@xxxxxxxxx>]

Subject: Data Model Advice

All,

My data modeling and SQL are oftentimes woefully inadequate, and I am
seeking advice on how to implement a particular database design.  My
database (so far) is comprised of the following types of tables:

The parts table contains parts of a particular type primary key is an
alphanumeric part number, some with dashes.

                       Table "public.parts"
   Column   |         Type          |            Modifiers
------------+-----------------------+---------------------------------
 partno     | character varying(64) | not null
 partno_raw | character varying(64) | default NULL::character varying
 boxno      | integer               | not null
 slotno     | integer               | not null
Indexes:
    "pk_parts" PRIMARY KEY, btree (partno)

The parts_subs table contains part numbers different manufacturers
that are identical to something that already exists in the parts table

              Table "public.parts_subs"
   Column    |         Type          |     Modifiers
-------------+-----------------------+--------------------
 partno      | character varying(64) | not null
 partsub     | character varying(64) | not null
 partsub_raw | character varying(64) | not null
 boxno       | integer               | not null
 slotno      | integer               | not null
Indexes:
    "pk_parts_subs" PRIMARY KEY, btree (partno, partsub)

I'm trying to determine, and I'm seeking advice on:

 How to maintain the original part number format but create the
primary key with only alphanumeric.  I currently have a 'before insert
or update' trigger to strip the non-alphanumeric characters, but it
doesn't permit the insertion of a record because it is null on insert
(for some reason).  The trigger function, which I'm trying to make as
flexible as possible, is:

CREATE OR REPLACE FUNCTION fixup_partnumbers() RETURNS TRIGGER AS
$fixup_partnumbers$
BEGIN
    IF (STRPOS(TG_TABLE_NAME, '_subs') > 0) THEN
        NEW.partsub := REGEXP_REPLACE(NEW.partsub_raw, E'(\\W|_)', '', 'g');
    ELSE
        NEW.partno := REGEXP_REPLACE(NEW.partno_raw, E'(\\W|_)', '', 'g');
    END IF;
    RETURN NEW;
END;
$fixup_partnumbers$ LANGUAGE plpgsql;

Thank you in advance for any advice and assistance you can provide.

-- Gary Chambers

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general