Is it possible to have more than one foreign key in a normalised database schema?

Question

Address (**addressID**, houseNumber, postcode,
    roadName, city, county)  
Reservation (**reservationID**, roomNo, *leadGuestID*,
    guest2ID*, *guest3ID*, arrivalDate, departureDate,
    *addressID*, amountOutstanding)  
Guest (**guestID**, firstName, lastName, adultFlag,
    telephoneNo, *addressID*, emailAddress)  
Room (**roomNo**, *roomTypeName*)  
RoomType (**roomTypeName**, floor, basePrice,
    extraAdultPrice, extraChildPrice)  

The above schema is supposed to be normalised to 4NF. But I cannot see how leadGuestID, guest2ID & guest3ID can be foreign keys, since they do not exist as primary keys in any other table. There are probably other issues.

Is it possible to have more than one foreign key in a normalised database schema?

Answer 1

Foreign keys don't have to reference primary keys (they can reference any unique key, and in some DBMSs can reference any indexed columns) and don't have to have the same name as the referenced columns.

Column names are a poor substitute for two logical concepts - domain and role. Domain refers to the set of values that are valid in a column. Role refers to the meaning or purpose of the column. For example, in a (manager, subordinate) relationship, the domain of both columns would be the set of valid employee identifiers, while the roles indicate who reports to who. Generally, when there is only one occurrence of a domain in a relation, there's no need to indicate the role.

The purpose of normalization is to eliminate redundant functional and join dependencies that create risks of inconsistent data. Before you can identify the current normal form or problems in a schema, it's necessary to determine and list the functional and join dependencies. It can't be done from column names. An understanding of the business domain of the data model may enable modelers to see possible violations quickly, but even experienced modelers make mistakes if they don't list the dependencies, so start there if you're concerned about the schema.

It's possible to have more than one foreign key in a relation even in the highest normal forms. Foreign key constraints in SQL are only a way of enforcing domain integrity, they don't affect dependencies and/or normalization in any way.

Note that I'm not saying leadGuestID, guest2ID, guest3ID is good design. The relationship between guests and reservations should probably be recorded in a separate table, but such designs don't violate the first 4 normal forms.

Answer 2

Slightly different notions of FK (foreign key) & FK constraint are used when discussing the relational model, and SQL uses others. It doesn't matter what NFs (normal forms) relations/tables are in.

Usually under the relational model we say there is a FK constraint from a set of attributes in one relation (value or base or expression) referencing that set in another relation when values under each attribute of the set in the referencing relation must appear under it in the referenced relation & the set of attributes is a CK (candidate key) int the referenced relation.

(In SQL a table's FK references a set declared as PK (primary key) or UNIQUE. This is more or less what we could call a relational foreign superkey, referencing a superkey rather than a CK, and a SQL PK is more or less a superkey. But it doesn't really make sense to talk about superkeys or CKs in SQL, and a given relational term typically means a different thing in SQL.)

We call the referencing set a FK. The associated constraint is also sloppily called a FK. We declare sufficient FK constraints so that when the ones we declare hold the others have to hold too.

To use a database (interpret or update) we need to know what rows are supposed to go into each base relation in a given situation. To determine constraints (superkeys, CKs, FKs, etc) we also need to know what possible values each base is constrained to hold per what situations can arise.

Reservation (reservationID, roomNo, *leadGuestID*,
    *guest2ID*, *guest3ID*, [...])  
Guest (**guestID**, [...])  

Presumably the values under each of leadGuestID, guest2ID & guest3ID in Reservation has to appear as a value under Guest guestID, which you show is a CK of Guest. If so, the following FK constraints hold:

Reservation (leadGuestID) references Guest (guestID)
Reservation (guest2ID) references Guest (guestID)
Reservation (guest3ID) references Guest (guestID)

Re FKs under normalization:

When normalizing to higher NFs we replace a base relation by multiple projections of it. So the original & components all have the same set of values under each attribute. So for every CK of a component, each other component with those attributes must have the same subtuples under them. So every time a component's CK's attributes all appear in another component there is a FK in the other to that CK. When two components both have a given set as CK there are FKs both ways.

Similarly if there was a FK constraint from an original to some base's CK then every component with the referencing attributes has a FK constraint to that base. Similarly if there was a FK from some base to an original then for each component with the original's CK attributes as a CK there is a FK constraint from the base to that CK. Even if a component only has some of the attributes of a referencing or referenced set of its original in a FK constraint with a base, the component might have a FK constraint with that base involving subsets of that prior FK.

When you replace a base by components you lose the FKs involving the base so besides adding declarations per the new FKs above you might have to add other declarations per FKs that were implied by the lost ones.

PS Relationally, a PK is some CK you decided to call the PK; PKs don't matter to FKs or normalization. In SQL, a PK is just some UNIQUE NOT NULL you decided to call PK.