• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Meaningless vs Meaningful Primary Keys (1 Viewer)

KKilfoil

Registered User.
Local time
Today, 09:53
Joined
Jul 19, 2001
Messages
336
Especially the line...[snip]...My design standard is holistic and the naming convention is, indeed, a major contributor to the readability of the sample schemas. The use of meaninful keys would undermine the naming conventions (and has done in the meaningful schema that does not, and cannot meet my design standard) and I did qualify these points appropriately. But this is not the point being discussed - we're focusing merely upon the advantages and disadvantages of meaningless/meaningful keys. The example simply illustrates that wheter either approach is more readable is in the eye of the beholder (iow it is subjective)....[snip]...
I think I agree with Doc_Man on this.

Everyone needs to follow a formal NAMING convention. Yours certainly has its merits, but there others that are good as well.

However, the CONTENTS of the fields and the NAMES of the fields are two distinct things.

The top structure of your .jpg can be followed PRECISELY and still employ MF keys (unless your arbitrary naming convention requires that nnnID fields only be autonumbers).

I think I'm going to have to agree with some of the other posters here. Although you have been stating that your intent is to fully examine MF vs ML key usage, your responses only seem interested in supporting arguments for meaningless keys, and you dispute ANY comments supporting meaningful keys.
You need to read my post again!
I respectfully suggest that YOU read your posts again, because Doc_Man's comments are right on. What you meant to say, and what you said, may not be quite the same here!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
18,012
Mike, to be honest, some kids at 4th & 5th grade ARE learning how to use computers at rudimentary levels. Not far enough along (yet) to get into this discussion - but my first grandson, age 11 and a 5th grader, blows me away with his ability to search the web, put together little messages in e-mail, and otherwise use the computer in a way that didn't even exist 20 years ago.

But at my age, 20-year-olds are still in the class of "kids" - which proves Einstein right once again... it's all relative.

KKilFoil and I spotted something in your posts and we called you out on it. I'll go a step farther because I think I have some insight into what might have happened here.

Very frequently, when we write, we immerse ourselves in the topic and start thinking at a very fast clip. At some point we reach a mental state where we focus on the goal and not the path. And at that exact point, we start writing with assumptions as to whether other readers will follow what we say even though we are long since departed from an environment familiar to our readers. It is a form of writer's tunnel vision.

I'm totally as guilty of it as anyone else. I'm just more aware of it because of one of my hobbies - writing amateur fiction. Having studied the writing process in many ways - including technical writing for the government - I have learned to watch for this switch to tunnel-vision mode. Have you ever read a REALLY good technical manual? Watch for tunnel-vision situations where the writer allows jargon or assumed terms to creep in. It'll open your eyes to the REAL meaning of "good technical writing."

Mike, I'm glad you understand that I'm trying to constructively criticize your efforts by finding weak points so you can shore them up. But in this format, it is sometimes hard to be more eloquently supportive - we have a posting size limit and we try to spread ourselves around to others asking for help. Both of these often lead to a brusque style of writing to save space and time. That brusque style can seem brutal sometimes. If I ever step too hard on your toes, let me know.
 

KKilfoil

Registered User.
Local time
Today, 09:53
Joined
Jul 19, 2001
Messages
336
By the way, at the risk of hijacking this thread a bit, my own naming convention seems quite similar to yours, but with a difference:

On the 'many' side of the relationship, I use the suffix 'FK' where you use ID. So in your example, the PK in the Employee table would be called [EmployeeID] and the related field in the Invoice table would be [EmployeeFK].

I feel this has two advantages:

1) EVERY foreign key is specifically denoted as such for rapid recognition, and every primary key is specifically denoted (as xxxID is only used for PKs)

2) The two fields used in the join are named differently, so it is easier in the query design grid to pick fields, as you can skip the step of selecting the table. If you work exclusively in SQL mode, then this is not as useful.
 
Last edited:

Mike Smart

Registered User.
Local time
Today, 06:53
Joined
Feb 14, 2007
Messages
53
I think I agree with Doc_Man on this.

Everyone needs to follow a formal NAMING convention. Yours certainly has its merits, but there others that are good as well.

However, the CONTENTS of the fields and the NAMES of the fields are two distinct things.

The top structure of your .jpg can be followed PRECISELY and still employ MF keys (unless your arbitrary naming convention requires that nnnID fields only be autonumbers).

I think I'm going to have to agree with some of the other posters here. Although you have been stating that your intent is to fully examine MF vs ML key usage, your responses only seem interested in supporting arguments for meaningless keys, and you dispute ANY comments supporting meaningful keys.

I respectfully suggest that YOU read your posts again, because Doc_Man's comments are right on. What you meant to say, and what you said, may not be quite the same here!

I think there's a danger of going off on a tangent here into naming conventions and that isn't what the thread is really about. My schema simply supports the assertion that whether one convention is more readable than the other isn't objectively debateable. I won't expand upon my naming convention for foreign/primary keys here since that is all precisely defined elsewhere.

It is no article of faith for me that ML keys are preferable otherwise I wouldn't have created this thread to solicit other ideas (and many have been very good) but I think that it is impossible to state that schemas are more readable with either approach since this depends upon so many other factors.

Thanks for your input.
 

Mike Smart

Registered User.
Local time
Today, 06:53
Joined
Feb 14, 2007
Messages
53
By the way, at the risk of hijacking this thread a bit, my own naming convention seems quite similar to yours, but with a difference:

On the 'many' side of the relationship, I use the suffix 'FK' where you use ID. So in your example, the PK in the Employee table would be called [EmployeeID] and the related field in the Invoice table would be [EmployeeFK].

I feel this has two advantages:

1) EVERY foreign key is specifically denoted as such for rapid recognition, and every primary key is specifically denoted (as xxxID is only used for PKs)

2) The two fields used in the join are named differently, so it is easier in the query design grid to pick fields, as you can skip the step of selecting the table. If you work exclusively in SQL mode, then this is not as useful.

This is rule 2.3 in the design standard "Foreign keys always have exactly the same name as the related primary key". The rule works particularly well with rule 2-6 for the stated reasons.

I think I must stay focused in this thread but I would be obliged if you would advise (perhaps by private mail) if you fundamentally disagree with rules 2.3/2.6 (another one that has a holistic element) and, if you do, we could start a new thread to discuss but I think that it is more likely that you'll see it more as simply one of many potential alternatives.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Sep 12, 2006
Messages
13,892
i have read this thread with interest, i have been a regular contributor here, but recently have not had so much time to post.

I must say i now do tend to add autonumber keys to every table, to use as pk/fk links, knowing that these will not change, and that users can then modify data that might otherwise be hard to modify. Of course this doesn't always work, as sometimes the link between tables consisted of segmented data.

i rarely have the same field name in multiple tables - i prefer to prefix the variable with characters that idenitifies the table it comes from - eg dt for delivery ticket, po for purchase order etc.

one reason for autonumber keys, I am sure is that having primary keys as numeric rather than text must make the access operation more efficient, although it may adding a theoretically unnecessary additional key to each (or at least some) table

ultimately the solution is surely whatever works for the developer - if the data is normalised properly then surely the identifier names used are somewhat irrelevant - as long as you can write code quickly and find the relevant references it doesn't matter does it

for instance i still have loads of global variables - sometimes i include a grouped selection of them within a defined type, so i can use intellisense - alternatively i sometimes save values in the registry, and other times save values in temporary tables. all of these solutions work fine for me - i have very large apps, but i know them intimately, although i know many excellent posters here dont use global vars

Once you get a large app, i would think you stil lhave difficulties irrespective of the naming convention you use, as you will still end up with vast numbers of similarly described field names - for example, in Sage (a UK accounting package) the various tables have vast numbers of fields, the use of which is often not immediately apparent.

overall, i think you can't be totally prescriptive in these things. does that mean i come down on your side doc-man?

if this is all trash, tell me and i'll delete the posting!

thanks
 

KeithG

AWF VIP
Local time
Today, 06:53
Joined
Mar 23, 2006
Messages
2,592
I always like to use a meaningful primary key if possible but I am not agianst using a meaningless key. I don't really think there is a correct answer here because I look at database development like an art. Therefore we are all artist and have our own styles. For example If you asked two painters to create a painting of your local mall I bet the two paintings would not turn out the same.

I have been following this post since the begining and I have really in joyed the topic and just want to throw in my two cents.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
18,012
Gemma, I don't think "side" has anything to do with it. I've never hidden the fact that I'm a pragmatist when it comes to programming. If I can make it work with a little bit of percussive engineering ("bang on it to make it fit"), I will. But not enough to warp the whole structure.

Despite the stark dichotomy of meaningful or meaningless, I see the situation as a continuum of options. Mike and I have different emphases, but neither can truly be said to be outright wrong. So I can't say that "side" is relevant. I'm more pleased that you have found a philosophical thread to be of value to you. And I'm just a contributor. I'm certain that Mike Smart is overjoyed that people are paying attention to his thread even if some disagree with his basic positions.

It is through exchange of ideas - not rules or conventions - that we learn. My rules and Mike's conventions might equally be useless to you. Or you might find a useful nugget here and there. As Forrest Gump once said, "It happens." Or as Judy Tenuta says, "It could happen."

In fact, today I had to post a reply to someone regarding numbered keys; told him to ask himself if having a contiguously numbered key was that important. I.e. in the case being questioned, I was proposing a meaningless key. I'm not so totally against such keys as to never use them. I merely have a priority about how to decide whether a natural, relevant candidate key exists. If so, I prefer to use it. If not, then synthetic key here we come.

KeithG - you are right. Database design, because it involves more than just data layout, can indeed be an art. This is case where form follows function, and you need to know function first. Then and only then can the form of your database be properly expressed. D.E. Knuth's The Art of Computer Programming (I think I got the title right, my copies of volumes I, II, and III were taken by Katrina) immediately sets the record straight. As long as subjectivity and clever designs are possible, it ain't a science.

On the other hand, whether a particular database satisfies the requirements that led to its design... that IS a science, generally referred to under the category of Interrogative Logic. Which falls under the broader concept of "Computability Theory." But how you get from demand to design to database, ahhhh, there's the art.
 

ButtonMoon

Registered User.
Local time
Today, 13:53
Joined
Jun 4, 2012
Messages
304
3/ It isn't possible to create a composite alternate key (only a composite primary key).
Where did this myth originate from? It is possible to create composite alternate keys and has been since at least 2003 (and probably before) as far as I know. There are multiple other misconceptions mentioned here but that is possibly the worst.
 

Minty

AWF VIP
Local time
Today, 13:53
Joined
Jul 26, 2013
Messages
7,349
Holy thread resurrection Batman !
This is from over 10 years ago!
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:53
Joined
Oct 17, 2012
Messages
3,260
 

Attachments

  • ARISE.jpg
    ARISE.jpg
    50.4 KB · Views: 181

isladogs

CID VIP
Local time
Today, 13:53
Joined
Jan 14, 2017
Messages
13,942
Where did this myth originate from? It is possible to create composite alternate keys and has been since at least 2003 (and probably before) as far as I know. There are multiple other misconceptions mentioned here but that is possibly the worst.

@ButtonMoon
Having resurrected this ancient thread, I think you should explain exactly what you are referring to as 'composite alternate keys'
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
18,012
Talk about a blast from the past! I remember this discussion. Haven't changed my viewpoint of pragmatism, so haven't changed my viewpoint on the desirability of natural keys when they exist. But have NO problem whatsoever with using synthetic keys when the decision in favor of "natural" becomes unclear.
 

ButtonMoon

Registered User.
Local time
Today, 13:53
Joined
Jun 4, 2012
Messages
304
@ButtonMoon
Having resurrected this ancient thread, I think you should explain exactly what you are referring to as 'composite alternate keys'

A composite key is a key consisting of more than one attribute. An alternate key is just a key that isn't the "primary" key (alternate keys are sometimes called secondary keys, candidate keys or just keys).

Unfortunately the power of myths often increases with age.
 

isladogs

CID VIP
Local time
Today, 13:53
Joined
Jan 14, 2017
Messages
13,942
I already know and understand all those terms.

What I'm trying to get you to explain is exactly what you mean by a composite foreign/alternate key.
The reason is that I think you may be referring to something else.

Perhaps you can illustrate with a couple of screenshots
 

ButtonMoon

Registered User.
Local time
Today, 13:53
Joined
Jun 4, 2012
Messages
304
A composite alternate key is just an alternate key that has more than one attribute (nothing to do with foreign keys). For example, this table has two composite keys: (a,b) and (c,d).

CREATE TABLE tbl (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL
UNIQUE (a,b), UNIQUE (c,d));
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
18,012
I'm seeing this as just a multi-field index that isn't the primary key because, for complexity's sake (or perhaps simplicity's sake), you chose a synthetic/autonumber key as the PK.

If course we ALL realize that people use such indexes as key-level rather than field-level constraints (usually of the uniqueness variety) precisely because field level constraints have trouble once the requirement comes in to compare against other records.

And nomenclature-wise, I have seen the use of "key" in this context because, though we don't always think of it that way, an index IS based on a key field. Or more than one, in the situation mentioned by ButtonMoon. I admit it is an older nomenclature, but then again, we have established that I'm an older programmer.
 

isladogs

CID VIP
Local time
Today, 13:53
Joined
Jan 14, 2017
Messages
13,942
I deliberately wrote foreign / alternate to try & tease out what you meant.

First of all your create table syntax is incorrect in Access
This is a corrected version:
Code:
CREATE TABLE tblABCD (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, 
CONSTRAINT AB UNIQUE (a,b), CONSTRAINT CD UNIQUE (c,d));

As I suspected, you are creating 2 composite INDEXES.



Perfectly valid thing to do and I do it all the time - but I've not heard that referred to as composite alternate keys before
 

Attachments

  • CreateTableIndexes.PNG
    CreateTableIndexes.PNG
    13.4 KB · Views: 139

jdraw

Super Moderator
Staff member
Local time
Today, 09:53
Joined
Jan 23, 2006
Messages
13,360
Just thought I'd throw a little fuel on the fire.

I often create a separate autonumber PK on a junction table rather than using the combination of PKs from the members of the junction. Then use a composite unique index to prevent duplicates. I also am not familiar
with the term composite alternate keys.

There are oodles of articles on surrogate vs natural primary keys, alternate keys and indexes. Some arguments posed by proponents on either side will test your patience. I'm sure many are technically correct theory.
Whatever your style --- design, vet and build.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
18,012
Absolutely true, JDraw... as a pragmatist, I do what is necessary to make it work, staying within the lines as much as possible and straying when the situation demands it.

Which is why I sometimes break with the design principles espoused by Nicklaus Wirth in that I sometimes actually use a GOTO in my code. (Admittedly rare and hardly ever in a Class module).
 

Users who are viewing this thread

Top Bottom