SQL One-to-One Relationship Definition

I’m designing a database and I’m not sure how to define one of the relationships. Here’s the situation:

1. An invoice is created
2. If the product is not in stock then it needs to be manufactured and so a work order is created.

The relationship is one-to-one. However work orders are sometimes created for other purposes so the WorkOrder table will also be linked to other tables in a similar one-to-one relationship. Also, some Invoices won’t have a work order at all. This means I can’t define these relationships in the normal way by using the same primary key in both tables. Instead of doing this I’ve created a linking table and then set unique indexes on both fields to define the one-to-one relationship (see image).

_{(source: markevans.org)}
.

Is this the best way?

Cheers
Mark

EDIT: I just realised that this design will allow a single work order to be linked to an invoice and also to one of the other tables I mentioned via 2 linking tables. I guess no solution is perfect.

Answer

Okay, this answer is SQL Server specific, but should be adaptable to other RDBMSs, with a little work. So far as I see, we have the following constraints:

• An invoice may be associated with 0 or 1 Work Orders
• A Work Order must be associated with an invoice or an ABC or a DEF

I’d design the WorkOrder table as follows:

CREATE TABLE WorkOrder (
     WorkOrderID int IDENTITY(1,1) not null,
     /* Other Columns */
     InvoiceID int null,
     ABCID int null,
     DEFID int null,
     /* Etc for other possible links */
     constraint PK_WorkOrder PRIMARY KEY (WorkOrderID),
     constraint FK_WorkOrder_Invoices FOREIGN KEY (InvoiceID) references Invoice (InvoiceID),
     constraint FK_WorkOrder_ABC FOREIGN KEY (ABCID) references ABC (ABCID),
     /* Etc for other FKs */
     constraint CK_WorkOrders_SingleFK CHECK (
          CASE WHEN InvoiceID is null THEN 0 ELSE 1 END +
          CASE WHEN ABCID is null THEN 0 ELSE 1 END +
          CASE WHEN DEFID is null THEN 0 ELSE 1 END
          /* + other FK columns */
          = 1
     )
)

So, basically, this table is constrained to only FK to one other table, no matter how many PKs are defined. If necessary, a computed column could tell you the “Type” of item that this is linked to, based on which FK column is non-null, or the type and a single int column could be real columns, and InvoiceID, ABCID, etc could be computed columns.

The final thing to ensure is that an invoice only has 0 or 1 Work Orders. If your RDMBS ignores nulls in unique constraints, this is as simple as applying such a constraint to each FK column. For SQL Server, you need to use a filtered index (>=2008) or an indexed view (<=2005). I’ll just show the filtered index:

CREATE UNIQUE INDEX IX_WorkItems_UniqueInvoices on
    WorkItem (InvoiceID) where (InvoiceID is not null)

---

Another way to deal with keeping WorkOrders straight is to include a WorkOrder type column in WorkOrder (e.g. ‘Invoice’,’ABC’,’DEF’), including a computed or column constrained by check constraint to contain the matching value in the link table, and introduce a second foreign key:

CREATE TABLE WorkOrder (
     WorkOrderID int IDENTITY(1,1) not null,
     Type varchar(10) not null,
     constraint PK_WorkOrder PRIMARY KEY (WorkOrderID),
     constraint UQ_WorkOrder_TypeCheck UNIQUE (WorkOrderID,Type),
     constraint CK_WorkOrder_Types CHECK (Type in ('INVOICE','ABC','DEF'))
)
CREATE TABLE Invoice_WorkOrder (
     InvoiceID int not null,
     WorkOrderID int not null,
     Type varchar(10) not null default 'INVOICE',
     constraint PK_Invoice_WorkOrder PRIMARY KEY (InvoiceID),
     constraint UQ_Invoice_WorkOrder_OrderIDs UNIQUE (WorkOrderID),
     constraint FK_Invoice_WorkOrder_Invoice FOREIGN KEY (InvoiceID) references Invoice (InvoiceID),
     constraint FK_Invoice_WorkOrder_WorkOrder FOREIGN KEY (WorkOrderID) references WorkOrder (WorkOrderID),
     constraint FK_Invoice_WorkOrder_TypeCheck FOREIGN KEY (WorkOrderID,Type) references WorkOrder (WorkOrderID,Type),
     constraint CK_Invoice_WorkOrder_Type CHECK (Type = 'INVOICE')
)

The only disadvantage to this model, although closer to your original proposal, is that you can have a work order that isn’t actually linked to any other item (although it claims to be for an e.g INVOICE).