Data Model Advice

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