On 9 May 2010, at 6:49, Rick Yorgason wrote:
> So, your first suggestion would look like this:
>
>> reginfo(order_id, product_id, reginfo1_columns, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES
order_items)
>
> For the sake of illustration, let's say that order_item's foreign key to this table is NOT NULL.
>
> So, if the product in question uses regtype1, then the reginfo2 columns are NULL, and vice versa. If the product
doesn'tuse any registration, then both the reginfo1 and reginfo2 columns are NULL.
>
> The problem is, how do I express that requirement in a constraint? And without updating the schema every time I add
anew product?
You can do that with a CHECK constraint: CHECK (reginfo1 IS NULL OR reginfo2 IS NULL).
I see you're talking about multiple columns for both now though. Considering that you can add them as columns to the
reginfotable you could change that into a 1:1 relation with their own respective tables and change reginfo.reginfo1 and
reginfo.reginfo2into nullable foreign keys.
> Your second suggestion would look like this:
>
>> reginfo(order_id, product_id, FOREIGN KEY(order_id, product_id) REFERENCES order_items)
>>
>> reginfo1(order_id, product_id, reginfo1_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo)
>>
>> reginfo2(order_id, product_id, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo)
>
> Well, at that point, the reginfo table is redundant, and the reginfo1 and reginfo2 tables may as well reference
order_itemsdirectly, which is exactly what I have, minus my problematic constraint.
A constraint like that can't be expressed directly, as you mention, but writing BEFORE INSERT and UPDATE triggers that
performthe necessary checks and return NULL if they fail (and raise an error of course) isn't that difficult.
> My assumption is that most people would simply give up and assume that this constraint is too difficult to express in
SQL,and just rely on the business logic never being wrong. I was hoping that wasn't the case :)
The business logic "always" gets it wrong at some point, if it weren't just because business logic tends to perform the
sameaction on data from multiple code paths. That's not (necessarily) bad design in the business logic, it's just that
thetranslation from user interface to data objects often isn't a straight one. I wouldn't be surprised if your
differenttypes of orders originate from different locations in the user interface, for example.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4be6782910411440911937!
