SQL Server 2008: can 2 tables have the same composite primary key?

Question

In this case, tables Reserve_details and Payment_details; can the 2 tables have the same composite primary key (clientId, roomId)?

Or should I merge the 2 tables so they become one:

clientId[PK], roomId[PK], reserveId[FK], paymentId[FK]

Answer 1

In this case, tables Reserve_details and Payment_details; can the 2 tables have the same composite primary key (clientId, roomId) ?

Yes, you can, it happens fairly often in Relational Databases.

• (You have not set that tag, but since (a) you are using SQL Server, and (b) you have compound Keys, which indicates a movement in the direction of a Relational Database, I am making that assumption.)

Whether you should or not, in any particular instance, is a separate matter. And that gets into design; modelling; Normalisation.

Or should I merge the 2 tables so they become one:
clientId[PK], roomId[PK], reserveId[FK], paymentId[FK] ?

Ok, so you realise that your design is not exactly robust.

That is a Normalisation question. It cannot be answered on just that pair of tables, because:

• Normalisation is an overall issue, all the tables need to be taken into account, together, in the one exercise.

• That exercise determines Keys. As the PKs change, the FKs in the child tables will change.

• The structure you have detailed is a Record Filing System, not a set of Relational tables. It is full of duplication, and confusion (Facts¹ are not clearly defined).

• You appear to be making the classic mistake of stamping an ID field on every file. That (a) cripples the modelling exercise (hence the difficulties you are experiencing) and (b) guarantees a RFS instead of a RDb.

Solution

First, let me say that the level of detail in an answer is constrained to the level of detail given in the question. In this case, since you have provided great detail, I am able to make reasonable decisions about your data.

If I may, it is easier the correct the entire lot of them, than to discuss and correct one or the other pair of files.

• Various files need to be Normalised ("merged" or separated)

• Various duplicates fields need to be Normalised (located with the relevant Facts, such that duplication is eliminated)

• Various Facts¹ need to be clarified and established properly.

Please consider this:

• Reservation TRD

  • That is an IDEF1X model, rendered at the Table-Relation level. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows feet; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.

• The Predicates are very important, I have given them for you.

  • If you would like to information on the important Relational concept of Predicates, and how it is used to both understand and verify the model, as well as to describe it in business terms, visit this Answer, scroll down (way down) until you find the Predicate section, and read that carefully.

Assumption

I have made the following assumptions:

• Given that it is 2015, when reserving a Room, the hotel requires Credit Card details. It forms the basis for a Reservation.

• Rooms exist independently. RoomId is silly, given that all Rooms are already uniquely Identified by a RoomNo. The PK is ( RoomNo ).

• Clients exist independently.

  • The real Identifier has to be (NameLast, NameFirst, Initial ... ), plus possibly StateCode. Otherwise you will have duplicate rows which are not permitted in a Relational Database.

  • However, that Key is too wide to be migrated into the child tables ², so we add ³ a surrogate ( ClientId ), make that the PK, and demote the real Identifier to an AK.

• CreditCards belong to Clients, and you want them Identified just once (not on each transaction). The PK is ( ClientId, CreditCardNo ).

• Reservations are for Rooms, they do not exist in isolation, independently. Therefore Reservation is a child of Room, and the PK is ( RoomNo, Date ). You can use DateTime if the rooms are not for full days, if they are for short meetings, liaisons, etc.

• A Reservation may, or may not, progress to be filled. The PK is identical to the parent. This allows just one filled reservation per Reservation.

• Payments do not exist in isolation either. The Payments are only for Reservations.

  • The Payment may be for a ReservationFee (for "no shows"), or for a filled Reservation, plus extras. I will leave it to you to work out duration changes; etc. Multiple Payments (against a Reservation) are supported.

  • The PK is the Identifier of the parent, Reservation, plus a sequence number: ( RoomNo, Date, SequenceNo ).

Relational Database

You now have a Relational Database, with levels of (a) Integrity (b) Power and (c) Speed, each of which is way, way, beyond the capabilities of a Record Filing System. Notice, there is just one ID column.

Note

1. A Database is a collection of Facts about the real world, limited to the scope that the app engages.

2. Which is the single reason that justifies the use of a surrogate.

3. A surrogate is always an addition, not a substitution. The real Keys that make the row unique cannot be abandoned.

Please feel free to ask questions or comment.