Work request & Work Order - which table should have the foreign key?

[Mark]

I have two tables, tblWorkRequest (Primary field WorkRequestID) and
tblWorkOrder (Primary field WorkOrderID). A work order will not always have
a work request, the majority of work requests will have a Work Order as long
as the request is accepted. It is a one to one relationship as each request
can only have one work order. Is it better to have a foreign key
WorkOrderID in tblWorkRequest or foreign key WorkRequestID in tblWorkOrder?

thanks

 

[Jamie Collins]

I have two tables, tblWorkRequest (Primary field WorkRequestID) and
tblWorkOrder (Primary field WorkOrderID). A work order will not always have
a work request, the majority of work requests will have a Work Order as long
as the request is accepted. It is a one to one relationship as each request
can only have one work order. Is it better to have a foreign key
WorkOrderID in tblWorkRequest or foreign key WorkRequestID in tblWorkOrder?

To avoid redundancy, I'd recommend a relationship table... then they
can both be the FK!

Constraint each column as unique and make the PK the composite of both
columns e.g. in SQL code:

CREATE TABLE WorkThingys (
WorkRequestID INTEGER NOT NULL UNIQUE
REFERENCES tblWorkRequest (WorkRequestID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
WorkOrderID INTEGER NOT NULL UNIQUE
REFERENCES tblWorkOrders (WorkOrderID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
PRIMARY KEY (WorkRequestID, WorkOrderID)
);

Jamie.

--