Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes (although I am presuming the
invention RoomID represents a surrogate to stand in place of the Natural Key
for the purpose of Primary Key)

I trust you have advised to OP by private email that this table would
require two unique non nullable indexes to avoid duplicates.

Remember when you add these little ID fields you also need to ensure you
maintain data integrity.

Data integrity and consistency is of the utmost importance to the users. It
is a real problem in the real world; without a second unique non-null index
on the RoomNo field you could have 50 Room 101s. You may respond by saying
that you knew that, my suspicion is that the OP does not, but then if you
have already emailed them (or sent a correction to this newsgroup) about
your omission all is well.

RoomNo would appear to satisfy the requirements of a natural PK
(familiarity, minimality, stability, simplicity).

Please note very little would be gained by discussing Surrogate vs Natural
on this forum (there are justifications on some use of surrogates when the
natural PK fails some of the tests for suitability (familiarity, minimality,
stability, simplicity) however the automatic use of little ID fields
indicates an (let's be gentle (g)) unconsidered approach).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...
Try this: