[Pdx-pm] Eliminating circular table relations.

[David E. Wheeler]

On Jan 31, 2007, at 1:15 PM, Michael G Schwern wrote:

> Let's hear those answers.  I'm building up a case for Postgres here  
> and the more ammo the better.

You build the tables as you described, then you create a view joining  
them as two separate tables, one for meat_account and one for  
mushroom account. Something like this (untested PostgreSQL code):

CREATE TABLE _account (
     id                  SERIAL PRIMARY KEY,
     name                TEXT,
     address             TEXT,
     phone               TEXT
);

# sub-type
CREATE TABLE _meat_account (
     id                  INTEGER PRIMARY KEY REFERENCES account(id),
     drippings           BOOLEAN,
     gravy               TEXT
);

# sub-type
CREATE TABLE _mushroom_account (
     id                  INTEGER PRIMARY KEY REFERENCES account(id),
     type                TEXT
);


CREATE VIEW meat_account AS
SELECT a.id as id, name, address, phone, drippings, gravy
   FROM _account a, _meat_account ma
  WHERE a.id = ma.id;

CREATE VIEW mushsroom_account AS
SELECT a.id as id, name, address, phone, type
   FROM _account a, _mushroom_account ma
  WHERE a.id = ma.id;

Then update preferences so that the user has permission to access  
only the views, and not the tables. Next you create rules on the view  
to handle INSERTS, UPDATES, and DELETES. They look something like this:

CREATE RULE insert_meat_account AS
ON INSERT TO meat_account DO INSTEAD (
   INSERT INTO _account (id, name, address, phone)
   VALUES (NEXTVAL('_account_id_seq'), NEW.name, NEW.address,  
NEW.phone);

   INSERT INTO _meat_account (id, drippings, gravy)
   VALUES (CURRVAL('_account_id_seq'), NEW.drippings, NEW.gravy);
);

Create similar rules ON UPDATE and ON DELETE. Then you can treat the  
views exactly like tables and completely ignore the _account and  
_meat_account tables.

I went into some detail on this approach in presentations at the last  
two OSCONs. I used it to model OO inheritance in the database  
(meat_account inherits from account). You can get the slides here:

   http://conferences.oreillynet.com/presentations/os2006/ 
wheeler_david.pdf
   http://www.kineticode.com/docs/polymorphic_database_design.pdf

I also use it extensively in Object::Relation, my ORM module that I  
expect to get back to hacking sooner or later.

HTH,

David