PK – FK multi-fields question

Your "Request" table would have two keys to the "Office" table. This is similar to having two addresses for a customer, and "Ordered By" and a "Ship To".
For your example:

Request Table
Request Table_ID (PK)
SomeField
Requesting_Office_ID (FK)
Providing_Office_ID (FK)
SomeMoreFields



==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)

Andy,

Offices table has Fields, [Office One], [Office Two], [Office Three]?

Skip,
That was just an example to explain my point

johnherman,

Request Table
Request Table_ID (PK)
SomeField
Requesting_Office_ID (FK)[red] --> Office.ID ???[/red]
Providing_Office_ID (FK)[red] --> Office.ID ???[/red]
SomeMoreFields

I would have to try it. Neved had 2 fields as FK to the same field in other table.
Also have to try it in query builder - when I drag Office table, how will that work/display in QB.....?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Isn't this really a parent/child table structure?

I'm most familiar with bill of material where you have part/component. Each component has a part record, and at the bottom of the bill the part record describes the purchased/source material. So you drill down or up thru a recursive process whatever way you need to go.

So here You'ld have office/Providing office. The requesting office is built into the structure.

Yes, you can have two ID's to the same table. So yes, both the Requesting_Office_ID and Providing_Office_ID are foreign keys to the Office table, linked via the Office_ID keyfield in the Office table.
Other examples:
In Health Care, there are often two diagnosis codes for a patient's visit or admission. However, in Health Care, these are usually labeled Primary and Secondary Diagnosis.
And, as my previous example, there could be multiple addresses for a business (shipping address, billing address, etc).

Here is an example from health care where I am joining to the diagnosis table twice:

select count(distinct medical_record_num) uniq_pat
from encounter_main m
inner join diagnosis dx1 on m.encounter_sid = dx1.encounter_sid and dx1.code in ('07030', '07032')
inner join diagnosis dx2 on m.encounter_sid = dx2.encounter_sid and dx2.code = '5715'
where m.admission_date between to_date('20140101','yyyymmdd') and to_date('20141231','yyyymmdd')






==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)

As the others already said, it's perfectly okay to have two FK to the same parent table, you just also need two relationships, two FK Keys, eg named FK_Yourtable_RequestedOffice and FK_Yourtable_InfoOffice, each matching its FK field to the Office PK field.

Bye, Olaf.