Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-31-2019, 12:56 PM   #1
RhiannonM
Newly Registered User
 
RhiannonM's Avatar
 
Join Date: May 2019
Location: Tassie, Australia
Posts: 19
Thanks: 11
Thanked 2 Times in 2 Posts
RhiannonM is on a distinguished road
Primary Keys...Autonumber or Field Name?

Hi Guys

Just a quick question...if I have tables that are a single field (eg. tblCategory has field name CategoryName and that's it) do I really need an Autonumber field as well or can the CategoryName be the primary key?

And yes, the category names are unique and not duplicated anywhere else in other tables.

Cheers!
RhiannonM

RhiannonM is offline   Reply With Quote
Old 05-31-2019, 01:19 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,775
Thanks: 50
Thanked 1,088 Times in 1,069 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

Hi. The use of an Autonumber field for primary keys has always been a personal choice. It was never a requirement. What is required is to have a primary key. Using a number for a primary key or index is much better than using a text field.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 05-31-2019 at 02:24 PM.
theDBguy is online now   Reply With Quote
The Following 2 Users Say Thank You to theDBguy For This Useful Post:
RhiannonM (05-31-2019), Tera (06-01-2019)
Old 05-31-2019, 01:22 PM   #3
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,105
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

I tend to have surrogate keys that would be the autonumber. Could the Category name change at all.?
You will probably say No now, but further down the line, you never know.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
RhiannonM (05-31-2019)
Old 05-31-2019, 01:24 PM   #4
RhiannonM
Newly Registered User
 
RhiannonM's Avatar
 
Join Date: May 2019
Location: Tassie, Australia
Posts: 19
Thanks: 11
Thanked 2 Times in 2 Posts
RhiannonM is on a distinguished road
Re: Primary Keys...Autonumber or Field Name?

Good point Gasman...I don't see a need to change in the future but...ya never know...so I'll err on the side of caution...thanks for that!
RhiannonM is offline   Reply With Quote
Old 05-31-2019, 07:26 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

The ONLY time I ever used a single-column field was a validation using an IN clause. As in, "was this keyword in the list of accepted keywords?" IF you are simply using the single-column table in this way, you do not need a synthetic key. In fact, technically speaking, you don't need ANY key. (You DO still need the unique index for search optimization.)

The only time you would need a PK is if there will ever be a child of this table, in which case you would then need the PK to support the relationship. It DOES NOT MATTER if you need to change something by adding a new keyword (for validation?) because you aren't copying a code from the table. I.e. this is NOT a lookup table or an encode/decode table. You don't have enough infrastructure in that table to use it for either purpose.

You need a PK or code value if/when this table takes on a new purpose besides list verification. If this were an SQL Server table it would need a PK because you can't update the table without one, but if this is a native Access table, that is not a requirement.

One final thought - this is a type of table where you would have to think long and hard about actually changing a record unless the record was incorrectly stored. Validation tables should not be edited unless you are ready to invalidate all previous tests run against that table. This isn't an "Access rule" but rather is in the nature of using this kind of lookup validation. At least, that was what I decided when I used this kind of lookup method.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-31-2019, 07:56 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,291
Thanks: 0
Thanked 534 Times in 530 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Primary Keys...Autonumber or Field Name?

How long are these category names? If storage is an issue, saving a number PK/FK would use less storage space. If saving CategoryName into related table, then you are replicating that value. Is that a problem? Maybe. Text values require more storage and are slower to index for sorting/searching processes. Will you notice the difference? Maybe. Depends on size of database and network performance.

I have used text field as primary key with no adverse impact. I worked with a proprietary app using Access as backend that generated unique IDs like 040698080(7+E,/+#XA$.

If category name were to change and you want the change to reflect in historical records and the name is PK, then build a relationship between tables and enforce cascade update or run an UPDATE action sql. Otherwise, it's just a new record in tblCategories.

@The_Doc_Man, "single-column field" in first sentence, expect you mean "single-column table".
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 05-31-2019 at 08:17 PM.
June7 is online now   Reply With Quote
Old 05-31-2019, 08:06 PM   #7
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 942
Thanks: 10
Thanked 195 Times in 185 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

Quote:
but if this is a native Access table, that is not a requirement.
This I would question. I've seen many an action query in Access that would not work because there was no primary key on 1 side of 1 or more tables. Perhaps I'm not taking that statement in the correct context.

As for the initial question, I think what is of primary importance up front is to understand the implications of a decision. Where I worked, the CMMS based database was designed around PK text fields. This means that wherever PK field DEPT was linked, you linked (for example) MACH to MACH (Machine Shop). That makes it so much easier when querying a PO table for example, because you didn't need DEPT table; otherwise you would because MACH would be some cryptic number such as 158 if it were an autonumber PK. Thus in a re-organization you could never change MACH to anything else, nor could you ever have a duplicate text value throughout the whole organization. In the unlikely event they ever HAD to change a text value for anything, they probably would have relied on cascade updates, although given the size of the db and the fact that the back ends were not Access tables, I doubt they were concerned about that.

For some, the whole concept might seem less than professional but I can assure you that the company was top notch when it came to IT, and was (and probably still is) a world class leader in computerized maintenance management systems (CMMS). I'm talking maybe 100 db's each with a huge number of tables, some with millions of records covering everything from soup to nuts (ICMS, RCA, FMEA, Purchasing, Safety, BOM, Contracting, Stores, Inventory, Maintenance, Scheduling, Utilization, real time condition monitoring, to name some). So the point of all that is that they are by no means amateurs yet use text based PK fields. That is why I say that the understanding of one's decision either way, replete with the limitations, advantages and any disadvantages of that decision is what's most important.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 05-31-2019, 10:01 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

Caught me in a mis-type, June7. Spot on - I meant single-column table.

Micron, the point is that this is a stand-alone validation table from its description (though if RhiannonM wishes to correct me, then I will stand corrected.) There is nothing with which to JOIN. There is no infrastructure, no code. It's just a list to be searched for IN/NOT IN as far as this discussion has gone. And I absolutely agree that if you have a "natural" text PK, use it. Synthetic keys have their place and I use them all the time. But in the context that we have seen so far, I haven't seen a need for an extra field.

As far as not being able to update an Access table that didn't have a PK? JUNCTION tables don't need a PK (because they aren't parents) and yet can be updated. If they couldn't, they would never have existed in the first place.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-01-2019, 03:26 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Primary Keys...Autonumber or Field Name?

if you do not include a numeric key AS WELL (you need a unique index on the category name ANYWAY) - then the database will store the longer category names text, rather than the shorter number key. If you change the category name, then every usage of the category name throughout your database will need updating, which will only happen if RI has been set appropriately in the relationships.

In general, I would always add a numeric key. There might be odd exceptions like a US state table. It's still a matter of choice, understanding the pros and cons - Micron's point about the text values being more transparent is a point to take into consideration.


[edit - TheDOCMan
you will still need to refer to the list when validating an entry, and you should still need to have the list included in your relationships to prevent you deleting a value from the list - unless you want to allow users to enter free text, rather than pick from a controlled list.]
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-01-2019, 04:40 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

Good point, Dave. Relational Integrity is important as an anti-deletion safeguard. I did not think of that facet last night, it was already getting quite late and June caught me in a late-night bit of sloppiness. One of the perils of being an insomniac.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-01-2019, 05:44 AM   #11
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 942
Thanks: 10
Thanked 195 Times in 185 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

Quote:
...if I have tables that are a single field (eg. tblCategory has field name CategoryName and that's it) do I really need an Autonumber field as well or can the CategoryName be the primary key?
I see nothing in that question that defines it as a "stand alone validation table", whatever that may be. I think the point I made is relevant and can be further explained for you.

Table DEPT contains only a single field of 4 alpha-only characters. It is the PK, thus by extension, it is the FK in every other table where it is found. If that makes it a validation table for you then that's fine. Your point about having nothing to join to is therefore lost on me, as is not having any code - in what , a table field?

My major point is that not everyone is hell bent on numeric keys, be they autonumber or whatever, and at least some who are not concerned are professionals. As long as one fully understands the ramifications of that approach when deciding, there is nothing decidedly wrong about it.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Tera (06-01-2019)
Old 06-01-2019, 08:11 AM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Primary Keys...Autonumber or Field Name?

@Micron

I see table DEPT as a table OF departments, (rather than just a single row), to use within the database as required. You might want to give these a 'meaningful' 4 character alpha code, or a numeric code.

eg
WARE , Warehouse
PROD , Production
OFFC , Office
FINC , Finance

The designated alpha code you pick might become less meaningful though if you need to add more departments, and need to change one of your descriptions, and as a result the alpha designation is no longer an "abbreviation" for exactly the same description. This wouldn't happen with a non-meaningful PK - you wouldn't need to change it.

If you use meanngful values, as a PK, then you need to cascade the changes throughout the database, If you use non-meaningful keys, you don't have to, so it's a matter of taste really.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-01-2019, 09:26 AM   #13
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 942
Thanks: 10
Thanked 195 Times in 185 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

gth - I don't know if the meaning behind that is just general comment or if it's about something you think I need to consider. Surely my posts indicated that not only is the db not mine, it is long established, worked well and was created by people a lot smarter than me. Perhaps they went that way because of legacy information/data.

To reiterate the message - meaningful text data as PK can work; you just need to understand the ramifications of that choice. end of message & no one has to agree that it is a wise choice because I'm not saying that. If anyone has questions about the db I will try to answer them. I worked with it on and off for 24 years and there were parts I never touched so I don't know all of it, but any advice for me about that db is kinda pointless.

Thanks.
Micron is offline   Reply With Quote
Old 06-01-2019, 11:53 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary Keys...Autonumber or Field Name?

I'm actually with Micron on this one - if you have a meaningful natural key, it is OK to use it as such. Less confusion for subsequent maintainers. If in the case described in this thread you made the (only) field the PK, it would by definition also be indexed, which would further aid any lookups, searches, relationships, or JOINs. There are many reasons to declare a synthetic key but at no point do I see any such reasons in this case under scrutiny.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-01-2019, 12:11 PM   #15
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,105
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Primary Keys...Autonumber or Field Name?

Again being curious and keen to learn, or get other opinions.

Is there any benefit in having everything 'the same way'? especially if multiple/new developers

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help, need to update table with 2 foreign keys with autonumber from primary keys. meelos Tables 3 11-23-2009 07:05 AM
The Use of Primary Keys and Autonumber The_Doc_Man Access FAQs 0 05-24-2007 09:18 AM
Autonumber Primary Keys Hickups Tables 2 08-20-2004 02:15 AM
2 Primary Keys + AutoNumber field help EPD Hater Tables 4 07-20-2003 10:55 AM
Primary Keys - Autonumber or Unique Field Value Cosmos75 Tables 8 06-26-2002 12:05 PM




All times are GMT -8. The time now is 12:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World