Hello,

My question is pertaining to how to properly link entities.

To make my case simpler I will just mention the 3 entities I am having trouble with.

HousingApp: (This entity will hold the records of the requests for room in a given hotel-like-institution.)

HousingRoom: (This entity will hold the rooms of the Hotel-like institution)

Assignment: (This entity will have foreign keys from both the HousingApp and HousingRoom entities and will hold the actual assignment to rooms)


The HousingApp entity, will have the following 3 fields main fields:

ApplicantID (Will be social security number)
ApplicantBeginDate (Request for housing begin date)
ApplicantEndDate (Request for housing end date)

Since applicants can have more than one application with different dates of stay in housing, I consider that these 3 fields will uniquely identify the entity occurrence and as a
result I created in the HousingApp entity a compound key as follows:

PRIMARY KEY (ApplicantID, ApplicantBeginDate, ApplicantEndDate).

This is my question,

Since the Assignment file entity will be comprised of foreign keys from both HousingApp and HousingRoom and since the foreign key coming from HousingApp has 3 fields:

1 How does one define the foreign key of the HousingApp in the Assignment file?

2 Would such foreign key be comprised of the three fields that makes the main key in the HousingApp entity?

How would the SELECT statement to return an assignment of a particular person and for a particular begin and end date look?

I thank you in advance for your great assistance.