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

create table foo_vf (
       id                       integer primary key,
       from_v                   not null references user_groups,
       to_f                     not null references f,
       default_q                references foo_vf_quantity,
       selected_q               references foo_vf_quantity,
       unique (from_v, to_f)
);

create table foo_vf_quantity (
       id                       integer primary key
       quantity                 number not null, 
       cost                     number,
       notes                    varchar(2000),
       vf                       not null references foo_vf,
       last_user_modifying      not null references users,
       last_modified            date not null
);

Is this bad practice?

Should I create the table, and then alter it to add the additional columns?

BTW, I know this isn't in normal form, etc... In this particular application, it seems appropriate.

Perhaps I should put the selected_f and default_f columns in another table?

What do you recommend?

create table foo_vf_quantity (
       id                       integer primary key,
       quantity                 number not null, 
       cost                     number,
       notes                    varchar(2000),
       vf                       integer not null,
       last_user_modifying      not null references users,
       last_modified            date not null
);

create table foo_vf (
       id                       integer primary key,
       from_v                   not null references user_groups,
       to_f                     not null references f,
       default_q                references foo_vf_quantity,
       selected_q               references foo_vf_quantity,
       unique (from_v, to_f)
);

alter table foo_vf_quantity add foreign key (vf) references foo_vf(id);

Again, without understanding much about what you're really trying to do, another possible way to structure the tables would be to add a status column to the foo_vf_quantity table. Now, you can remove the reference from foo_vf to the quantity table. This schema would look like:

create table foo_vf (
       id                       integer primary key,
       from_v                   not null references user_groups,
       to_f                     not null references f,
       unique (from_v, to_f)
);

create table foo_vf_quantity (
       id                       integer primary key,
       quantity                 number not null, 
       cost                     number,
       notes                    varchar(2000),
       vf                       integer not null references foo_vf,
       last_user_modifying      not null references users,
       last_modified            date not null,
       status                   varchar(10) check (status in ('default', 'selected'))
);

Seems to me that the _vf to _vf_quantity relationship is many-to-one.  So I don't understand why you'd need the _vf_quantity to have a key back to the _vf table, unless it were an entirely different _vf from the one already keying to it but that doesn't seem to be the case from your description.  (If it is instead many-to-many you'd need a separate mapping table but you already know that which is why I'm confused. :)

second, what kind of state is selected_q maintaining vs default_q?  Is this like the "user default" and "factory default?"  if so selected_q should probably be in a table keyed off user to provide more flexibility.  But if you really want one global value it is fine as it is.

You've posed an interesting problem. It's made an eensy bit more complicated in that the names of columns and tables don't really communicate what it's for.

Your original question has already been answered by Michael Bryzek -- just add the final referential constraint after BOTH tables have been created.

A couple of people have questioned why you'd ever have a circular situation like this. My own classic example of this is the notion of component->subcomponent. In the case of a car, you might have something like:

  Transmission (component)
     |
     v
  Clutch assembly--+  (component AND subcomponent)
     |             |
     v             v
  Clutch disc     Pressure plate

A person has the the option of buying an entire clutch assembly or just the clutch disc. In order to define prices (and other descriptive information, like where the parts originate, etc.) for the both components AND subcomponents while keeping the structure, you'd have to break it into at least two entities, with the circular references between them.

Back to the problem at hand, if I understand this correctly, shipments from a given origin to a given destination have a variety of prices (based on quantity) that can be assigned. One rate in the rate chart is marked as the "default" (e.g., most people send 3000 pounds of whatever at a time on that route, so we'll make that the standard). Another is what is "selected" (Jade wants to send 10,000 pounds, so we'll cut a good deal).

The problem as I see it is that the default_q attribute goes with the shipping route (foo_vf), while selected_q goes with a particular shipment along that route. If I understand this correctly, you actually need a third entity.

     create table foo_vf_quantity (
            id                       integer not null primary key,
            quantity                 number not null, 
            cost                     number,
            notes                    varchar(2000),
            vf                       integer not null,
            last_user_modifying      not null references users,
            last_modified            date not null
     );
     comment on table foo_vf_quantity is 'Pricing for available route';

     create table foo_vf (
            id                       integer primary key,
            from_v                   not null references user_groups,
            to_f                     not null references f,
            default_q                references foo_vf_quantity,
            unique (from_v, to_f)
     );
     comment on table foo_vf is 'Available routes';

     alter table foo_vf_quantity add foreign key (vf) references foo_vf(id);

     create table foo_shipment (
            shipment_id              integer not null primary key,
            route_id                 integer not null 
                                     references foo_vf( id ),
            selected_price_id        integer not null 
                                     references foo_vf_quantity( id ),
            ...
      ); 
      comment on table foo_shipment is 'Shipment for a particular route';

In this case, the selected price has moved to the foo_shipment table. One uses foo_vf to see what routes are available, foo_vf_quantity to see the pricing options for that route, and foo_shipment to actually ship something, recording the price option chosen.

It's possible I'm completely off-base in my understanding here. If so, then I apologize for muddying the waters further.

Seeing actual names of things makes it easier to keep the relationships in mind. I'm still a bit puzzled by the exact problem you're trying to solve, in the absence of some key details. For example, you mention that "second table tracks all the various volumes shipped, and the cost for sending those volumes." This would imply that the second table records actual shipments, not available prices for future shipments. In that case, I think this model is probably denormalized such that it won't represent everything.

However, as I said, I'm not sure I understand the whole thing. I'll bow out for now, in the absence of more detail. I'd hate to make things worse.