Re: Database design confusing pg_restore, and misc pg_restore issues

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't use 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 a new 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 reginfo table you could change that into a 1:1 relation with their own respective tables and change reginfo.reginfo1 and reginfo.reginfo2 into 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_items directly, 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 perform the 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 same action on data from multiple code paths. That's not (necessarily) bad design in the business logic, it's just that the translation from user interface to data objects often isn't a straight one. I wouldn't be surprised if your different types 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!