Re: Design question : Nullable FK & FK To Unique Key

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 09 Nov 2006 15:53:58 -0800
Message-ID: <1163116438.443768@bubbleator.drizzle.com>

David Portas wrote:
> krislioe_at_gmail.com wrote:

>> Hi gurus,
>>
>> Our Invoice may or may not has Orders before it. User can input order
>> and produce invoice from it, OR they can just input Invoice without
>> order. So we plan to have Nullable FK on Invoice.
>>
>> The question is :
>>
>> 1) What is the bad side of Nullable FK ?
>>
>> 2) FK from Invoice to Orders , not refering to PK , but to the Unique
>> Key of the Orders, what is the bad side of this ?
>>
>> Thank you for your help,
>> xtanto

>
>
> 1) What do you hope to gain from inserting a null for an order that
> doesn't exist? Nulls are always a compromise and they mean you have to
> deal with 3-value logic. Avoid them unless you have some specific
> purpose in mind.
>
> 2) In terms of relational design the choice of primary key makes no
> difference.

It seems everyone is understanding something different from what the OP posted. My take was the following:

CREATE TABLE order (
order_id NUMBER
invoice_id NUMBER);

CREATE TABLE invoice (
invoice_id NUMBER);

Since an order can be part of one and only one invoice (assuming no back orders) then one could create an order today and invoice it, filling in the invoice number, tomorrow.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 09 2006 - 17:53:58 CST