A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hostel Database Setup



 
 
Thread Tools Display Modes
  #11  
Old July 24th, 2006, 02:31 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default Hostel Database Setup


"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



  #12  
Old July 24th, 2006, 02:38 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default Hostel Database Setup


"Jamie Collins" wrote in message
oups.com...

andreainpanama wrote:
I want a way that every single person has a
client ID number.


Clients
=======
ClientID
LastName
FirstName
NationalityID


How do you know whether today's Jean Dupont from France is the Jean
Dupont from France who stayed here in July last year or is the Jean
Dupont from France who stayed here in July last year e.g. do you
require them to quote their ClientID number when repeat booking?


Since she hasn't specified any need to know more about the client than
nationality, I'm not sure this is significant. One Jean Dupont from France
is as good as another, except of course if they are staying there at the
same time. And even then the difference may not be significant in terms of
the database, depending on what she is using it for.

If she wants to identify down to the actual person level, she will need more
information, such as address. Even that is subject to change. However,
that may not really matter, since I'm not sure there's any realistic
downside in a hostel database of having two entries for the same person at
different addresses.

That does bring up an interesting point, however, which is that if she wants
to distinguish Jean Dupont from France from Jean Dupont from Belgium, she
would need to add the nationalities to the combobox.

Another approach could be to record their passport number, with perhaps
a different identiifer for locals.


That will go over well with clients :-).

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.


Sure, if you have the list handy and feel like entering them all.

-Amy


  #13  
Old July 24th, 2006, 02:44 PM posted to microsoft.public.access.tablesdbdesign
Craig Alexander Morrison
external usenet poster
 
Posts: 88
Default Hostel Database Setup

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

The database is there to maintain data integrity, not the desk clerk, what
if one person entered a room twice?

Without defining the natural key you are plain WRONG.

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.


What if the room numbers only required Text(3), how long is a Long Integer?
Answer 4 bytes.

This means you are WRONG again, unless they have more than 99 rooms on each
floor or more than 9 floors and then you might end up using 4 bytes. BTW 1TB
costs less than 1GB ten years ago.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Amy Blankenship" wrote in message
...

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



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





  #14  
Old July 24th, 2006, 02:50 PM posted to microsoft.public.access.tablesdbdesign
Craig Alexander Morrison
external usenet poster
 
Posts: 88
Default Hostel Database Setup

PMFJI

That will go over well with clients :-).

In many countries it is a legal requirement to show/deposit passports and
travel papers. I would expect this to become the norm in most countries.

Sure, if you have the list handy and feel like entering them all.

Many ISO lists ares readily available to import into ones databases.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Amy Blankenship" wrote in message
...

"Jamie Collins" wrote in message
oups.com...

andreainpanama wrote:



  #15  
Old July 24th, 2006, 03:00 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default Hostel Database Setup


"Craig Alexander Morrison" wrote in
message ...
In the real world, once the rooms have all been entered, most people
stop.


The database is there to maintain data integrity, not the desk clerk, what
if one person entered a room twice?


Why would desk clerks be entering room numbers? Presumably this an initial
setup thing that wouldn't ever need to be touched again unless the hotel
expanded or for some reason the room numbers changed.

Do you ever answer posts yourself, or is your sole purpose on this board to
try to impress people with your theoretical knowledge by putting down the
posts of people who do?

-Amy


  #16  
Old July 24th, 2006, 03:09 PM posted to microsoft.public.access.tablesdbdesign
Craig Alexander Morrison
external usenet poster
 
Posts: 88
Default Hostel Database Setup

Been here since 1994 when it was MSACCESS, probably answered ten of
thousands of questions, admittedly not so much in recent years.

And your point to that is what... please don't correct you when you are
wrong?

Is the database not there to prevent data inconsistency is that what you are
inferring?

Is it impractical to ensure data consistency?

Indeed the DATABASE should prevent not only the desk clerk from entering a
duplicate room number it should also prevent the APPLICATION(S) from doing
so too.

The original questioner is going to be running a business on this database,
it is not some toy example, do you think you should be a bit more concerned
about data integrity?

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...

"Craig Alexander Morrison" wrote in
message ...
In the real world, once the rooms have all been entered, most people
stop.


The database is there to maintain data integrity, not the desk clerk,
what if one person entered a room twice?


Why would desk clerks be entering room numbers? Presumably this an
initial setup thing that wouldn't ever need to be touched again unless the
hotel expanded or for some reason the room numbers changed.

Do you ever answer posts yourself, or is your sole purpose on this board
to try to impress people with your theoretical knowledge by putting down
the posts of people who do?

-Amy



  #17  
Old July 24th, 2006, 03:21 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default Hostel Database Setup


"Craig Alexander Morrison" wrote in
message ...
PMFJI

That will go over well with clients :-).

In many countries it is a legal requirement to show/deposit passports and
travel papers. I would expect this to become the norm in most countries.


Sure, but when you're wandering the Highlands and stop in any old where, see
if they *actually* ask for your passport like they're supposed to. More
than likely they just have you sign the register and go up to your room.


  #18  
Old July 24th, 2006, 03:22 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Hostel Database Setup


Amy Blankenship wrote:
Since she hasn't specified any need to know more about the client than
nationality, I'm not sure this is significant. One Jean Dupont from France
is as good as another


I disagree. The OP specified "I want to know how many different
individuals have walked through my doors".

Jamie.

--

  #19  
Old July 24th, 2006, 03:22 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default Hostel Database Setup

Gees Louise. All she wants to do is track what countries her clients are
from...

She's not running Fort Knox.

"Craig Alexander Morrison" wrote in
message ...
Been here since 1994 when it was MSACCESS, probably answered ten of
thousands of questions, admittedly not so much in recent years.

And your point to that is what... please don't correct you when you are
wrong?

Is the database not there to prevent data inconsistency is that what you
are inferring?

Is it impractical to ensure data consistency?

Indeed the DATABASE should prevent not only the desk clerk from entering a
duplicate room number it should also prevent the APPLICATION(S) from doing
so too.

The original questioner is going to be running a business on this
database, it is not some toy example, do you think you should be a bit
more concerned about data integrity?

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...

"Craig Alexander Morrison" wrote in
message ...
In the real world, once the rooms have all been entered, most people
stop.

The database is there to maintain data integrity, not the desk clerk,
what if one person entered a room twice?


Why would desk clerks be entering room numbers? Presumably this an
initial setup thing that wouldn't ever need to be touched again unless
the hotel expanded or for some reason the room numbers changed.

Do you ever answer posts yourself, or is your sole purpose on this board
to try to impress people with your theoretical knowledge by putting down
the posts of people who do?

-Amy





  #20  
Old July 24th, 2006, 03:27 PM posted to microsoft.public.access.tablesdbdesign
Craig Alexander Morrison
external usenet poster
 
Posts: 88
Default Hostel Database Setup

Further to my earlier reply

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.


Sure, if you have the list handy and feel like entering them all.


http://www.iso.org/iso/en/prods-serv...sts/index.html

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I setup an interactively editable database? Hunt4868 New Users 2 January 13th, 2006 04:08 AM
Toolbars, Drop-Down Menus Rick New Users 1 September 21st, 2005 11:17 AM
Access Error Message when opening database eah General Discussion 3 January 26th, 2005 11:04 AM
Productkey problem when installing office 2003 on network Stefan Schreurs Setup, Installing & Configuration 1 June 1st, 2004 11:16 PM
Office XP Ed Lester Setup, Installing & Configuration 1 May 27th, 2004 09:30 AM


All times are GMT +1. The time now is 04:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.