Primary Keys...Autonumber or Field Name? (1 Viewer)

Your examples for difficulties with alphabetic keys includes a couple of cases I find interesting, and both of them have natural key implications.

The periodic table uses abbreviations of element names from multiple languages. So hydrogen is H - easy choice. C is carbon, no work there. S for sulfur, not hard to guess. But what about sodium and potassium? Respectively, Na is an abbreviation of Natrium, Latin for salt, and K from Kalium, the Latin name for that mineral. And then there is antimony - Sb for "Stibnum" meaning "mark" in Latin - because it would make a silvery smear of a mark on something used for testing such things.

The mixed origins of the names led to one-letter abbreviations in some cases and two-letter abbreviations in most other cases. Using the abbreviations therefore would lead to some ugly keys. Fortunately, there is ANOTHER natural key there that makes it easier - atomic number, which corresponds 1-to-1 with names. So there is a natural key anyway - just not the abbreviated name. We have to consider ourselves lucky. Dimitri Mendeleev formulated the modern periodic table but didn't insist on using the RUSSIAN names of the elements even though he could have. Back then, the world used the common Latin names because chemistry was already multi-national. Can you imagine trying to get modern countries to agree over what to call certain elements?

https://www.rigb.org/insideout/elements/periodic/index.html

But states? Holey Moley was THAT ever a mess to get started. It was over a generation ago that the USA devised the state-name abbreviations, about the time that the US Post Office switched to computer-driven character-recognition mail sorting. But there, we had no natural numeric key to use because 13 of our states were created at the same time. At least there was SOME semblance of reason in picking the abbreviations, though Alaska got screwed. AL would have worked but Alabama got that one. Alaska got AK. Leaving Arkansas and Arizona to fight. Arizona took AZ because Arkansas got AR. All sorts of 2nd-letter collisions got resolved and we have learned to live with what we got. BUT... at least with those abbreviations, you only need two bytes, which is a really nice, short "natural" key even if it was derived from unnatural selection.

So net result, Dave, is that your first two examples still use natural keys, not synthetic ones. One is text, the other is numeric.

In some applications, I find that I have tons of simple lookup lists - status, rank, business type, category, transaction code, etc. After a while, it gets tedious to keep creating new tables and new forms for the user to maintain the values. Most of these "little" tables should be under the control of the user and the programmer never needs to be involved. I solved this problem back in the early 80's - Long before Access came into my life. My original version of this app was COBOL with IMS/DB as the underlying database. I reproduced it a second time using COBOL with DB2 and the version I am posting is obviously Access with ACE tables although the tables can be in whatever BE you want them to be. They should reside in the same database as the other tables of your application.

The concept of the app is to store all the "little" tables in a single table so that I only have to create a single interface. So there is a table of "tables" and the table details table. I import the forms/reports/tables into each new application so I never have to think about this basic requirement again. The app has changed very little over the years. It assumes a certain level of user authorization and that allows you to use the same concept for ALL tables, even those that must be controlled by the programmer because code needs to be written to handle different options. Things like States can also be stored here if you don't need links to cities or countries. A common solution like this one is per force limited in what it will support so not all tables can be stored here. Some of them need extra fields like countries or cities. And so I still make separate tables for the ones that don't fit my standard mold.

The codes table includes a sequence # field which is optional and allows you to specify a sort order different from alpha on the name field if that makes sense to the users. Sometimes a particular value is used so frequently, the users want it at the top of the list. The table also includes both short and long names which helps if you have limited room on a form or report. It also includes an active flag. You cannot delete an item once it is added so the only way to get rid of it is to flag it as inactive. When you create a query to use the list on your application forms, your primary sort is by the Active flag which makes all the True (-1) values sort to the top and then sort by sequence number and long name. That leaves the inactive items in the list (necessary or they will disappear when looking at an old record that includes one of these values) but gets them out of the way of current work. I also add code to the beforeUpdate event of the combo that uses one of these lists to prevent the user from selecting an inactive value.

How do i stop what I've started??

Seriously guys...this is way more information than my brain can deal with...I appreciate that you all have different opinions but I can't process any of it.

I just wanted a simple answer...certainly not 3 pages of posts to sift through.

How do I close the thread?

Cheers
RhiannonM

It's a discussion, it's the nature of forums. You aren't the only reader and this thread might be useful to others. You can ignore it from now on if you want since you have the info you needed. You might revisit it in a few months after working with db for a while and see if it makes more sense then. Or keep reading each post just to tickle your brain.

To reiterate summation: consensus is using text field as PK is fine and your single field tables are appropriate.

not a bad idea to mark it as solved though. Prevents some/most people from opening it to contribute something when it's already solved. Maybe that's in the thread tools drop-down? I dunno because I don't think I've ever started a thread here.

I apologise if I came across as rude.

Was not my intention.

RhiannonM

RhiannonM, YOU were not rude. We were merely overly entusiastic at the chance to jump in and discuss something that might have been helpful. If that overwhelms you at first, let us know. But also, as you progress, June7's advice will probably be right. When you come back to the thread for review, you might find that it makes more sense.

Hi Rhiannon
We rarely close threads here at AWF as others may wish to add something new in the future.
However I have marked it as solved in line with your earlier comments.

Good luck with your project.

You can also unsubscribe from the thread, so even if others contribute you will not get the emails.
The link is at the bottom of the email.?