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

in case, tables reserve_details , payment_details; can 2 tables have same composite primary key (clientid, roomid)?

or should merge 2 tables become one:

clientid[pk], roomid[pk], reserveid[fk], paymentid[fk] 

in case, tables reserve_details , payment_details; can 2 tables have same composite primary key (clientid, roomid) ?

yes, can, happens in relational databases.

• (you have not set tag, since (a) using sql server, , (b) have compound keys, indicates movement in direction of relational database, making assumption.)

whether should or not, in particular instance, separate matter. , gets design; modelling; normalisation.

or should merge 2 tables become one:
clientid[pk], roomid[pk], reserveid[fk], paymentid[fk] ?

ok, realise design not robust.

that normalisation question. cannot answered on just pair of tables, because:

• normalisation overall issue, tables need taken account, together, in 1 exercise.

• that exercise determines keys. pks change, fks in child tables change.

• the structure have detailed record filing system, not set of relational tables. full of duplication, , confusion (facts¹ not defined).

• you appear making classic mistake of stamping id field on every file. (a) cripples modelling exercise (hence difficulties experiencing) , (b) guarantees rfs instead of rdb.

solution

first, let me level of detail in answer constrained level of detail given in question. in case, since have provided great detail, able make reasonable decisions data.

if may, easier correct entire lot of them, discuss , correct 1 or other pair of files.

• various files need normalised ("merged" or separated)

• various duplicates fields need normalised (located relevant facts, such duplication eliminated)

• various facts¹ need clarified , established properly.

please consider this:

• reservation trd

  • that idef1x model, rendered @ table-relation level. idef1x standard modelling relational databases. please advised every little tick; notch; , mark; crows feet; solid vs dashed lines; square vs round corners; means specific , important. refer idef1x notation. if not understand notation, not able understand or work model.

• the predicates important, have given them you.

  • if information on important relational concept of predicates, , how used both understand , verify model, describe in business terms, visit this answer, scroll down (way down) until find predicate section, , read carefully.

assumption

i have made following assumptions:

• given 2015, when reserving room, hotel requires credit card details. forms basis reservation.

• rooms exist independently. roomid silly, given rooms uniquely identified roomno. pk ( roomno ).

• clients exist independently.

  • the real identifier has (namelast, namefirst, initial ... ), plus possibly statecode. otherwise have duplicate rows not permitted in relational database.

  • however, key wide migrated child tables ², add ³ surrogate ( clientid ), make pk, , demote real identifier ak.

• creditcards belong clients, , want them identified once (not on each transaction). pk ( clientid, creditcardno ).

• reservations rooms, not exist in isolation, independently. therefore reservation child of room, , pk ( roomno, date ). can use datetime if rooms not full days, if short meetings, liaisons, etc.

• a reservation may, or may not, progress filled. pk identical parent. allows 1 filled reservation per reservation.

• payments not exist in isolation either. payments reservations.

  • the payment may reservationfee (for "no shows"), or filled reservation, plus extras. leave work out duration changes; etc. multiple payments (against reservation) supported.

  • the pk identifier of parent, reservation, plus sequence number: ( roomno, date, sequenceno ).

relational database

you have relational database, levels of (a) integrity (b) power , (c) speed, each of way, way, beyond capabilities of record filing system. notice, there 1 id column.

note

1. a database collection of facts real world, limited scope app engages.

2. which single reason justifies use of surrogate.

3. a surrogate addition, not substitution. real keys make row unique cannot abandoned.

please feel free ask questions or comment.