[thelist] Tables referencing each other

Bill Moseley moseley at hank.org
Wed Mar 25 12:32:26 CDT 2009


I have a customer table.  Each customer has a profile.

    CREATE TABLE cust (
        id              SERIAL PRIMARY KEY,
        name            text,
        current_profile integer NOT NULL
    );

When a customer's profile is updated I insert a *new* row in the
profile table instead of updating an existing one.

This is done because there's also an "orders" table and each order is
associated with the profile that was current at the time the order
was created.  (Hence, "current_profile" in the cust table.)



I need to also associate all the customer's profiles to the customer
(e.g. to see a history) so I reference the customer from the profile
table:

    CREATE TABLE profile (
        id      SERIAL PRIMARY KEY,
        foo     integer, -- whatever
        cust    integer NOT NULL REFERENCES cust(id)
    );

And add in my constraint:

    ALTER TABLE cust ADD
        FOREIGN KEY (current_profile) REFERENCES profile (id)
            DEFERRABLE INITIALLY DEFERRED;

But, it's very awkward to create a new customer (below).

What's a better approach?  Using linked profiles?

    CREATE TABLE profile (
        id                  SERIAL PRIMARY KEY,
        foo                 integer, -- whatever
        previous_profile    int REFERENCES profile,
    );

Linked lists in SQL gives me a headaches. ;)



I have be able to track past profile rows from a number of tables.
Plus, it's important that it's easy to get at the "current" profile
quickly (with a simple join, for example).




Here's the ugliness:



    begin;

    insert into cust (name,current_profile) values ('foo',-1);
    INSERT 0 1

    select currval( 'cust_id_seq' );
     currval 
    ---------
           9
    (1 row)

    insert into profile (foo,cust) values (33,9);

    select currval( 'profile_id_seq' );
     currval 
    ---------
           1

    update cust set current_profile = 1 where id = 9;

    commit;


-- 
Bill Moseley
moseley at hank.org
Sent from my iMutt




More information about the thelist mailing list