Can A Foreign Key Be Used More than Once?

Question

Apologies for the newbie question.

The primary key of a table, such as Holiday, would be something like Holiday_ID. Holiday reference a get-away ticket that you can buy to go on a type of holiday, based on the ticket you buy.

Suppose I used Holiday_ID in a composite entity with Customer_ID to identify an instance of Holiday associated with customer, for whatever purpose.

However, suppose I also want to keep track of other information related to this instace: how much has the customer paid for the ticket, how much has the customer yet to pay for the ticket

I have two options:

a) I can create another composite entity. However, I am not sure if I can do that because I am not sure if you can use a particualr foreign key more than once

b) I can create a composite/associate entity, however, I am not sure if you can create a composite entity with more than two foreign keys?

Answer 1

To answer the technical parts of your question, once you create a composite unique or primary key, ONLY ONE record in the table can have the same values in the set of fields defined in that key. SO, no, you cannot reuse the holidayId key WITH THE SAME customer. You can use it with another, different customer if you wish.
Second, there is no limit to the number of attributes that can be included in a Unique or primary key. If you need, and if it's appropriate and conforms to the rules of normalization, the key can include all the attributes of the table.

Third, to answer your question below, Any column, or set of columns in a table can be defined as a Foreign Key, as long as it is also the primary key or unique key of some table in the database. And there can be any number of FKs defined in a table, they can even overlap. (you can have HolidayId as a FK, and also have HolidayID and CustomerId as a composite FK) the only restriction is that the FK must reference a Primary or Unique Key of some table in the database.(It can also be the same table the FK is in as well, as when you add a supervisorId to an employee Table that is a FK to the EMployeeId of the same employee table)

This example illustrates one of the problems of using surrogate keys without also using a natural key. to wit, what, exactly is a "Holiday"? Is Christmas 2016 the same "Holiday" as Christmas 2015? Is Christmas in Aruba the same holiday as Christmas in Hawaii?

and then, about the composite table to identify associations of customer with Holiday, is it the same association if the customer goes to Aruba on Christmas the next year, or a different instance? What does the row in the table represent if the customer wants 5 tickets?

The first thing that should be done in database design is a logical design which defines, as clearly and unambiguously as possible, in business terms, the meanings of the entities for each table in the database.