PK – FK multi-fields question

[Andrzejek]

Let's say I have Offices table:
[pre]
ID(PK) Office
1 Office One
2 Office Two
3 Third Office
4 Director
5 Office of my Boss
[/pre]
and I have another table where - among other information - I need to keep the data of (1) which office requested some information and (2) which office provides the information. Sometimes it is the same office, and sometimes not.

So if I have Another table:[tt]
ID (PK)
SomeField
Office_Request
AnotherField
Office_Info
SomeField
[/tt]
I know I can set [tt]Another.Office_Request[/tt] to be FK to [tt]Office.ID[/tt] field. But I also need to have the same for [tt]Another.Office_Info[/tt], but how can I assign 2 fields to be FK to one PK in one table?

I know I can set second table with Offices, but I don’t think that’s a good idea to have 2 tables with the same information.

So, how should I do it?



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.

 

[johnherman]

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)

 

[SkipVought]

Andy,

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

 

[Andrzejek]

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.

 

[SkipVought]

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.

 

[johnherman]

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)

 

[Olaf Doschke]

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.