Advice about Primary Keys & AutoNumbers 3

[Najemikon]

Hi,

I wonder if this great community can help me settle an argument or at least come to a happy compromise?

I've never been trained in database design. My teachers have been a handful of books & Tek-tips! My colleague though has years of database designing behind him & has a regimented, traditional approach. We've almost come to blows! Kidding, but I would like to hear peoples opinions on how you should approach setting the primary key in any given table.

I know there are three options:
1) Choose a field of unique data.
2) Choose multiple fields that should be unique, but can cover each other if not.
3) Create an AutoNumber column.

I always pick option 3, without question. Access created AutoNumber for that very purpose as far as I know. Anyway, I find it easier to deal with in relationships & coding. My colleague passionately disagrees & pursues option 1. At the end of the day, it's probably down to personal preference, but some of his comments make me wonder. I do have tables that don't need to link to others, but still have a primary key column. He says it's bad database design to just create one everytime. Maybe, but I don't trust other columns to be truly unique & prefer to play safe.

Should you always create a primary key without fail or can tables work fine without one at all? An AutoNumber can't be changed (a security I like), so if you choose a column containing unique data that is altered later, are dependant relationships at risk? Should you always aim to use an integer as the primary key or is text ok?

Over the years I've read great stuff here about designing tables, but not really forcing a choice. I seem to remember seeing something about corrupted data because of poorly chosen keys, but maybe I dreamed it!

I'd appreciate your comments!

 

[DaveInIowa]

Like so many things in life, using terms like always and never can always sometimes get you into trouble. The ability to define your own key field or let the DBMS do it for you is there for a reason depending on the task at hand.

That said, I also would pursue option 1 but I wouldn't be held to it. If a field(s) of reasonable length is/are a candidate for primary key, I would use them over an autonumber field. For instance, if you were creating a Zip Code master file with fields ZipCode, City, State and Country, would you pick ZipCode as your key field or would you add an irrelevant autonumber field? If this field best identifies the row, I would use it instead of an autonumber. This way if you needed to build an index over that field for some other purpose, that would be one additional index the DBMS has to manage.

One of the definitions of a table in a relational database is that no two rows are identical so generally speaking you should define a unique primary key. Control files with just a handful of records might be one exception but even then it can't hurt to add a key.

As to your question of choosing a column that is altered later, if there was a chance that this field's value could change in the future I wouldn't consider it for a PK (Although with referential integrity and cascading updates this may not be a problem).

 

[TheAceMan1]

How are ya Najemikon . . . . .

If you [blue]stop thinking AutoNumber[/blue] and [blue]think Unique Identifier[/blue], you'll be taking a big step forward.

Access doesn't care about record order in a table. It does care that each record is [blue]Uniquely Identified![/blue] After all . . . . these are the fields we use in our table relationships.

Now . . . since the final design of the values in this field simply have to be unique . . . [purple]where is it written, it has to be some complex, 5 field, impressive looking, 32 character value![/purple] . . . You tell me? My point here is, the simpler the better. Show me a 5 field compound/complex primarykey and I'll bet ya any day I can replace it with one field! This is truly a case where simplicity has great power . . . [blue]espcially when your pulling your hair out trying to manipulate those complex keys.[/blue] (I've never understood it, but some programmers just can't seem to live without a complexity that blows their mind!).

Given the above, albeit there are those rare cases where compound/complexity is needed, but again . . . they are rare. This is where you come in as the designer. Its your decision what to do with these keys! I'm asking . . . unless the boss has a gun to your head or you decide otherwise, whats wrong with keeping it simple? You'll certainly allow your mental energies to move on to bigger and better things.

As for the use of autonumber myself, I've all but stopped using it for the following reason: faq700-184

Your thoughts!

See Ya! . . . . . .

 

[r937]

Show me a 5 field compound/complex primarykey and I'll bet ya any day I can replace it with one field!

okay, this i gotta see


here's the table, please let me see you replace the primary key:

create table mydata
( code1 char(3) not null
, code2 integer not null
, acct varchar(11) not null
, trantype char(4) not null
, trandate datetime not null
, mydata1 varchar(9)
, mydata2 varchar(37)
, primary key ( code1, code2, acct, trantype, trandate )
)


r937.com | rudy.ca

 

[Golom]

Rudy


create table mydata
( theNumber Counter
, code1 char(3) not null
, code2 integer not null
, acct varchar(11) not null
, trantype char(4) not null
, trandate datetime not null
, mydata1 varchar(9)
, mydata2 varchar(37)
, primary key ( theNumber )
)


or were you hoping for one that has actual relevance to the data?

Obviously the replacement is simple ... it's just that it no longer corresponds to the business problem and probably severly distorts the way that records can be stored and retrieved. Don't know about you but I see this sort of thing far too often in databases. All the "real" database logic is buried somewhere in an application.

 

[LittleSmudge]

My 2penny-worth

If option 1 if an obvious and simple solution exists. Then go for it.

If not, then normally plump for option 3

The exception being linking tables in between two main tables to create a many-to-many.
These linking tables are usually option 2


eg.
A lookup table of Departments in a recent personnel database consisted of 2 fields

tblDept
DeptId Text(4)
DeptName Text(25)

The 4 char Dept code is in common use throughout the org to abbreviate the Dept. So it was simple to make that the Prime Key

In other tables the Foreign Key is then a Text(4) rather than a long int.


However, many other tables use an automatically generated "Unique Identifier" as their primary key so that I don't have to worry about there being two Sally Jones s in the Person table etc. ( Or Miss Jones leaving the company and then returning for a second period of employment at a later date !)


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Najemikon]

Thanks for taking the time to reply, guys. And always a pleasure to read your posts AceMan!

I can see that I have probably been too blinkered, but thus far, I am now even more convinced I've been doing it right.

I have a customer table, but we don't need postcodes (that's the proper name for a zipcode! ). I don't force entry, so it can't be a PK. I have one for equipment & serial numbers are not always to hand & can change if a fault results in a swap-out, so that's no good. My contacts table (links as one to many to the customer base) is the closest candidate, but peoples names aren't unique. Especially when one person is a contact for four customers.

Nope, as far as I can tell, I've always done the right thing choosing to add an AutoNumber. In the future though I'll bear in mind the other method as it may have its place.

I suppose what I needed more than anything was reassurance. Because I haven't been trained at Database School, the new chap is overly critical & in his own way, just as blinkered as I think I have been. I keep pointing out that it works & does some pretty cool things (usually courtesy of here!), but no, he's more concerned that I put a prefix (tbl, frm, etc) at the front of my object names.

So cheers again! I move forward with new found confidence.

 

[LittleSmudge]

What we're talking about here Najemikon is sometimes referred to as the Natural Key v Unnatuaral Key debate.

And that debate has been raging since Prof Codd first penned the rules on Normalisation.


Natural Keys have their place
( And they are the only real way of guaranteeing uniqueness )
but in the practical and real world you can get a very long way very much quicker and easier with a simple incrementing number.


Once you've build an app with four or five field natural PrimeKeys and then had them linking to lots of other tables
( Think how the relationship diagramme is beginning to look ) and then found that some tables a Prime Key consists of the four FK field of your original table plus two more in this one - then .. ..

Well you see why some of us look out for simple Natural Keys and use them when appropriate but otherwise will use AutoNumbers to simplify the job.


'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[DaveInIowa]

Najemikon - From the context of your last post, it seems as though you are confusing primary keys and foreign keys. For example, a serial number for an equipment table is an excellent candidate as the primary key. This value will never change. If a swap-out is needed, you would change the foreign key serial number value in a separate linking file that relates the piece of equipment with whomever/whatever it belongs too, but the serial number associated with that piece of equipment remains the same.

 

[Najemikon]

Thanks for the definition of Natural / Unnatural. I'd never heard that term before. Makes a whole lot of sense.

I do know what you mean by primary & foreign, Dave, but I didn't think through my example! We have situations where we don't need the serial number or we are waiting for the record to be updated while the initial problem is logged. There has to be room for it to stay blank for an indefinite period of time.

 

[r937]

or were you hoping for one that has actual relevance to the data?

i was hoping to see the key replaced


your example merely adds another column, which does not really support the claim that an autonumber will simplify things, if you know what i mean

;-)

r937.com | rudy.ca

 

[Golom]

Rudy

... does not really support the claim that an autonumber will simplify things

[InJest]
But ... but ... but ...

1 is less than 5 isn't it?

Isn't that simpler?
[/InJest]

You're right of course. I guess the message is that a PK's real function in life is to relate real-world entities to the structures that represent them in the database. It's not to relegate things that actually do uniquely identify an entity to being mere attributes of some invented key whose only virtue is uniqueness. Nor is it to obey some archaic rule invented by some guy named Codd.

 

[TheAceMan1]

Howdy [blue]Golom![/blue] . . . . .

Thanks for the indirect backup! . . . Ya know . . . not just in life . . . but there are those things . . . we simply have to learn the hard way! The only problem is that with Access . . . [blue]you can have your mind totally blown in the process![/blue]

[purple]Highest respect to you . . . . Sir![/purple]

See Ya! . . . . . .

 

[MajP]

If a single, simple unique field does not naturally exist I am a big fan of autonumbers because they make nice simple joins. But at the same time they do not ensure uniqueness. So I am always careful if uniqueness of the record is determined by multiple fields, then create an index from these fields but do not make them a complex primary key.
So assume you have tblPersonnel with firstName, middleName, and lastName as fields (and there will never be two people with the same first, middle, and last name). I would make an auto number the primary key, but also make an index from firstName, middleName, and lastName and set it unique. This way I have the simplicity of an autonumber for linking, but never inadvertently add the same person in the database twice.

 

[TheAceMan1]

r937 . . . . .

I did mention that there are rare cases!

[blue]Would you not consider this rare![/blue]

See Ya! . . . . . .