Обсуждение: need simple strategy for universal extension table

Instead of putting the same 12 columns on every table (these 12 columns
contain info about who created the record and when, for example)... It may
be more efficient to make a new table to hold that data.

This new table would act as a universal extension table.  Instead of having
FKs back to any particular table, it would contain regular keys that point
back to whatever table::record is its source.

I was thinking of using two keys only:

xmOwnerInfo
COL1 PK* oidParentTable
COL2 PK* oidParentTablesRecord
COL3-15 [attribs]

Thoughts?

--
TJ Talluto
torpedo51 at yahoo dot com

On Tue, 09 Nov 2004 19:20:20 GMT, TJ Talluto <tj@getlostspammers.com> wrote:
> This new table would act as a universal extension table.  Instead of having
> FKs back to any particular table, it would contain regular keys that point
> back to whatever table::record is its source.
>
> I was thinking of using two keys only:
>
> xmOwnerInfo
> COL1 PK* oidParentTable
> COL2 PK* oidParentTablesRecord
> COL3-15 [attribs]
>

I am doing something similar to this, though at this point I'm not
using it for storing creator/updator fields.  I need to be able to
group any arbitrary set of rows from multiple tables together and
apply sets of attributes to them.  I have a central 'entity' table
that holds the table name and the value of the 'id' column from that
table.  Then I attach two triggers to each table whos rows I want to
track. I am using tablename and a BIGSERIAL 'id' column because OIDs
on tables

  1) are only a 32 bit INT and I may very well wrap around on them,
whereas a BIGSERIAL is a 64 bit INT and

  2) the fact that OIDs may go away in a future version of PG.

Here's exactly what I'm doing (NOTE: this is for 8.0beta, so you will
have to adjust the quoting on the trigger functions for 7.x):

CREATE SCHEMA func;
CREATE SCHEMA entity;

CREATE TABLE entity.authority_list (
        id              BIGSERIAL PRIMARY KEY,
        entity          BIGINT,
        entity_type     text
) WITHOUT OIDS;

CREATE FUNCTION func.add_entity_entry () RETURNS TRIGGER AS $func$
        BEGIN
                INSERT INTO entity.authority_list (entity,entity_type)
                     VALUES (NEW.id,TG_ARGV[0] || '.' || TG_RELNAME);
                RETURN NEW;
        END;
$func$ LANGUAGE 'plpgsql';

CREATE FUNCTION func.remove_entity_entry () RETURNS TRIGGER AS $func$
        BEGIN
                DELETE FROM entity.authority_list
                    WHERE entity = OLD.id AND
                                entity_type = TG_ARGV[0] || '.' || TG_RELNAME;
                RETURN OLD;
        END;
$func$ LANGUAGE 'plpgsql';

CREATE TABLE someschema.sometable ( id BIGSERIAL, name TEXT );

CREATE TRIGGER sometable_add_entity_trig
        AFTER INSERT ON someschema.sometable
        FOR EACH ROW
        EXECUTE PROCEDURE func.add_entity_entry(someschema);

CREATE TRIGGER sometable_remove_entity_trig
        BEFORE DELETE ON someschema.sometable
        FOR EACH ROW
        EXECUTE PROCEDURE func.remove_entity_entry(someschema);


I am supplying the schema name to the trigger because the relation
name passed in as TG_RELNAME is the schema unqualified table name and
I have the same table name in several schemas.

Any comments on any of this would be very welcome.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer

Since nobody has mentioned any nuances about mapping these keys to system
tables in this particular database, I'll assume this is a good idea for
now.

--
TJ Talluto
torpedo51 at yahoo dot com