PK – FK multi-fields question
PK – FK multi-fields question
(OP)
Let's say I have Offices table:
ID(PK) Office 1 Office One 2 Office Two 3 Third Office 4 Director 5 Office of my Bossand 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:
ID (PK)
SomeField
Office_Request
AnotherField
Office_Info
SomeField
I know I can set Another.Office_Request to be FK to Office.ID field. But I also need to have the same for Another.Office_Info, 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.
RE: PK – FK multi-fields question
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)
RE: PK – FK multi-fields question
Offices table has Fields, [Office One], [Office Two], [Office Three]?
RE: PK – FK multi-fields question
That was just an example to explain my point
johnherman,
Request Table
Request Table_ID (PK)
SomeField
Requesting_Office_ID (FK) --> Office.ID ???
Providing_Office_ID (FK) --> Office.ID ???
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.
RE: PK – FK multi-fields question
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.
RE: PK – FK multi-fields question
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)
RE: PK – FK multi-fields question
Bye, Olaf.