"Craig Alexander Morrison" wrote in
message ...
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)

Whether Andrea chooses to designate the RoomID as a PK is her choice.
However, I'd suggest she use it at a minimum to establish the relationships
with other tables, since it uses less space than the actual text room
number.

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

Not really. It's her own hostel. I doubt she's overly concerned she might
accidentally enter the same room twice in the database :-).

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

Well, if you're a bank, yes. If you're a little hostel who just wants to
track clients more easily than a spread sheet, that's probably not your
primary concern.

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.

In the real world, once the rooms have all been entered, most people stop.

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).

Or it indicates that in practice I've found that automatically adding an
autonumber to each table eventually comes in handy. For instance, what if
you repainted all the rooms and someone accidentally switched two of the
room numbers around? This is easy to fix with a surrogate key, harder with a
natural key. It's hardly unconsidered, considering g that there are a
large number of highly skilled developers that agree with me.
http://www.dbpd.com/vault/9805xtra.htm

I think it's really rude to hijack a user's simple request for help with a
theoretical debate that goes far beyond the question at hand and delves into
esoteric points that the user probably isn't to a point in her development
career where she is ready to care about them. If I'm wrong and she is as
concerned about the theory behind what she's doing as just getting the
database to work, she should read the article posted above for a more
complete discussion of the issue, rather than your one point harangue that
doesn't give much context for her to make an informed decision.

-Amy