Forum OpenACS Q&A: Response to SQL: how do you do this?

Wow, I guess I really muddied the waters. I should have been more clear from the get-go.

I'm tracking freight costs for particular shipping routes. The first table tracks the particular shipping route (A->B), and the second table tracks all the various volumes shipped, and the cost for sending those volumes.

Here's what I've come up with:

-- tracks costs for freight from a vendor to a facility
create table freight_vendorfacility (
       id			integer primary key,
       -- from vendor to facility
       from_vendor		not null references user_groups,
       to_facility		not null references facility,
       default_quantity		integer,
       selected_quantity	integer,
       unique (from_vendor, to_facility)
);

create table freight_vend_fac_quantity (
       id			integer primary key,
       -- thousands of pounds
       quantity			number not null, 
       cost			number,
       notes			varchar(2000),
       vendor_facility		not null references freight_vendorfacility,
       last_user_modifying	not null references users,
       last_modified		date not null
);

alter table freight_vendorfacility add foreign key (default_quantity) 
references freight_vend_fac_quantity(id);

alter table freight_vendorfacility add foreign key 
(selected_quantity) references freight_vend_fac_quantity(id);

That's the real, non-obfuscated version, at least what I have so far. I'm still working out the details. The selected and defaults are currently set globally.

You have multiple freight routes, and multiple volumes for each route. Example:

ROUTE 
A->B  

volume: 5000 lbs
cost: $1.00/lb

volume: 10000 lbs
cost: $0.98/lb

volume: 15000 lbs
cost: $0.95/lb

One of these should be the default value, and one should be the currently selected value. Both can be changed.

It seems to me that in this circumstance, using circular constraints makes sense. It's a first for me.