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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Do I need a sequential primary key?
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 |
#2
|
|||
|
|||
Do I need a sequential primary key?
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 |
#3
|
|||
|
|||
Do I need a sequential primary key?
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. "Fred" wrote: 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 |
#4
|
|||
|
|||
Do I need a sequential primary key?
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. -- Milton Purdy ACCESS State of Arkansas "Webtechie" wrote: 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. "Fred" wrote: 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 |
#5
|
|||
|
|||
Do I need a sequential primary key?
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 "Webtechie" wrote in message ... 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. "Fred" wrote: 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 |
#6
|
|||
|
|||
Do I need a sequential primary key?
On Thu, 10 Sep 2009 11:34:05 -0700, Webtechie
wrote: 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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Do I need a sequential primary key?
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. "John W. Vinson" wrote: On Thu, 10 Sep 2009 11:34:05 -0700, Webtechie wrote: 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. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Do I need a sequential primary key?
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. |
Thread Tools | |
Display Modes | |
|
|