×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PK – FK multi-fields question

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 Boss
 
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:
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

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)


RE: PK – FK multi-fields question

Andy,

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

RE: PK – FK multi-fields question

(OP)
Skip,
That was just an example to explain my point smile

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

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.

RE: PK – FK multi-fields question

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)


RE: PK – FK multi-fields question

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close