If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Work request & Work Order - which table should have the foreign key?
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 |
#2
|
|||
|
|||
Work request & Work Order - which table should have the foreign key?
Mark wrote: 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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
User-Specific WorkTables: How to handle MakeTable queries? | (PeteCresswell) | General Discussion | 22 | April 21st, 2006 03:08 PM |
Table problem | Redwood | Database Design | 29 | April 3rd, 2006 04:58 PM |
Make Table Query - Sorting Errors | Fred | Running & Setting Up Queries | 19 | February 22nd, 2006 09:41 PM |
Query is not updatable - | Doug Johnson via AccessMonster.com | Running & Setting Up Queries | 3 | January 21st, 2006 12:36 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |