[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