need simple strategy for universal extension table

[TJ Talluto]

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

[TJ Talluto]

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

[Mike Rylander]

On Tue, 09 Nov 2004 19:20:20 GMT, TJ Talluto <[e-mail]> 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
mr*******@gmail.com
GPLS -- PINES Development
Database Developer

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster