Do I need a sequential primary key?

[Webtechie]

Hello,

I am designing a guest database. Guests leads will be entered into the
Guest Table each day. There should be about 300 leads entered into the guest
table each day.

So I am thinking that I need a primary key.

Scenario #1
========
Table - tblGuest
Primary key = GuestID - autonumber

Now if the guest were ever deleted (no sure why we would need to), it would
mess up my sequential order

GuestID FirstName LastName
1 Tom Jones
2 Susan Smith * deleted
3 Matt Francis

Then I would end up with

GuestID FirstName LastName
1 Tom Jones
3 Matt Francis

So I might need to change my design:

Scenario #2
=======
Table - tblGuest
Primary key = tblGuestID - autonumber
Field to reference = GuestID - number

I would get a max on each transaction and then assign it to my GuestID

tblGuestID GuestID FirstName LastName
1 1 Tom Jones
2 2 Susan Smith * deleted
3 2 Matt Francis (AFTER
GETTING MAX GUESTID)

Question:

Is this a good design? Do I even need to worry about sequential order for a
data entry table? If I just used the autonumber ID, even if a guest gets
deleted that shouldn't throw my referencing the other guests off for
reporting.

I'm leaning toward Scenario #1, but wanted to get the input of those that
know Access table design better than me.

Thanks,

Tony

 

[Fred]

As a sidebar, I assume that "guest" means an instance of a person stayign at
the hotel, and not the person who stayed. So a particular person visitn
twice constitutes 2 "guests"

An autonumber primary key should not be expected to follow any rules like
"sequential" or store any information. It's just a unique number for the
record.

If you are thinking about adding an additional field with a number in it,
the first step is to shut off yor computer and decide (without using Access
terminology) what exactly that number is supposed to mean, i.e. what
information it is to store. Then turn your computer back on and implement
what you decided.


Sincerely,

Fred

 

[Webtechie]

Fred,

Not sure where you are coming from, but well, thanks for at least responding.

I am creating a table to keep guests data. We have guests come to our
facility and are invited to our facility. I keep their name, address,
emailaddress, whether they came or not and other information for that guest.

I was hoping some of the guys good at designing could share insight to
whether in designing tables you need a key that goes sequentially or not.

Is it a good idea to create a scenario such as this:


Table - tblGuest
Primary key = tblGuestID - autonumber
Field to reference = GuestID - number

I would get a max on each transaction and then assign it to my GuestID

tblGuestID GuestID FirstName LastName
1 1 Tom Jones
2 2 Susan Smith * deleted
3 2 Matt Francis (AFTER
GETTING MAX GUESTID)

I'm thinking of creating reports and referencing that the key in other joins
with other tables.

My thought is that I don't need a key that goes sequentiallly as long as
each record key is unique.

Is that correct? Is it a better design to have a table key that goes
sequentially?

Thanks.

 

[golfinray]

The key does not have to be sequential, an autonumber is never guaranteed to
be sequential. I you absolutely need a sequential number, you can generate
one.

 

[Jeff Boyce]

Unless we share the same definition of the term "guest", there's little
chance the suggestions you get match your situation. Please define your
terms...you know your "domain" better than we ever can.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

My thought is that I don't need a key that goes sequentiallly as long as
each record key is unique.

Is that correct? Is it a better design to have a table key that goes
sequentially?

Only if you have some business need for a sequential number. There's nothing
in Access itself that would require or even benefit from it.

An Autonumber ID won't necessarily be sequential but it will provide a unique,
unchanging, non-repeating ID for the record. Normally that's all that you
would need.

 

[Webtechie]

Thanks John and Milton,

That is what is I was looking for. I was thinking that I didn't need a
sequential order for a key. I figured as long as it was unique that was good
database design. I just wanted to confirm it with folks who know Access
better than me.

Thanks again.

 

[Fred]

Hello Webtechie,

Sorry if I was a little abstract. Besides saying what the other folks said,
the additional thing I was saying was:

It was looking like you were contemplating adding a second Guest # column
of some type. If so, the only reason for that second number would be to
store some information about the guest. For example, to know which guest is
the 100th customer, even if the PK autonumber field is not recording that.
If so, your post was putting the cart before the horse, and talking about
Access details on how to populate that field but skipping over / not first
defining what that field should contain. In that case, saying "shut the
computer off" is a common fun & decisive way of saying get your field
definitions down before you start cluttering/impairing/enabling a denial
of/obscuring that thought process with Access implementation details.