Primary Key 'Natural or autoIncrement

Question

I've always designed primary keys based on some real world entity such as product code. However it seems many ppl
feel that primary keys should always be the autoIncrement type. Indeed an application development tool like Lightswitch forces you to take this approach. I've looked at this and understand the logic.

I decided that my next application would follow this paradigm.
However I've immediately run into a problem. Reduced to it's minimum there are two tables

Two tables

TBLHEADERS
ID                                    AUTOINCREMENT  (PK)
QDATE                             DATE/TIME
FK_TO_JOBS                     INTEGER

TBLJOBS
JOBID                              AUTOINCREMENT (PK)
JOBDESCRIPTION             SHORT TEXT                  eg XYZ-ABC
WEIGHT                           DOUBLE

I have to make a relation between these two tables FK_TO_JOBS to ID

If I now make a query so the user can add entries to tblheaders so that it shows the jobid then anything typed into the jobs field just changes the value in the jobs table instead of my usual method that would ensure that any value typed would have to match with a value in the jobs table.

Hope someone understands what I'm trying to say, not sure even I do!

Answer 1

Hi Andy. Are you saying tblJobs has a one-to-many relationship with tblHeaders? Meaning, each JobID can have many headers but a header can only have one job? If so, the usual form setup is to use a form/subform configuration with tblJobs on the main form and tblHeaders in the subform. Hope it helps...

Answer 2

I've always designed primary keys based on some real world entity such as product code. However it seems many ppl
feel that primary keys should always be the autoIncrement type. Indeed an application development tool like Lightswitch forces you to take this approach. I've looked at this and understand the logic.

I decided that my next application would follow this paradigm.
However I've immediately run into a problem. Reduced to it's minimum there are two tables

Hi Andy,

It depends on your development house style if your preference goes to natural keys or synthetic keys.

In my way of of database development I only use synthetic keys. Together with a strict naming convention on the table_name and the id_name gives me to possibility to generalize all relations within a database. For the synthetic keys I use the Autonumber type.

Though in principal any (unique) key in a table can be the primary key, I always have the synthetic key as primary key: the less bytes of a key, the faster the retrievel of data.

With your example, a typical join would read as:

    SELECT * FROM (TBLHEADERS INNER JOIN TBLJOBS ON TBLJOBS.JOBID = TBLHEADERS.FK_TO_JOBS)

In my notation it would read as:

    SELECT * FROM (Header_tbl INNER JOIN Job_tbl ON Job_tbl.Job_id = Header_tbl.Job_id)

Imb.

Answer 3

Hi,
Yes the JOBID can have many headers. but a header can have only one job. The JOBID is unique by the way.

This is actually going to be driven by a vb.net from end. At this point I just want a single form created from a
query (could be just a dataview at this point). The form would have to display:

(header) ID (machine generated)
QDate         (usually Now() )
JOBDESC     This is the one that causes the problem

This is the usual table design I'd use:

TBLHEADERS
ID                                    AUTOINCREMENT  (PK)
QDATE                             DATE/TIME
JOBID                              SHORT TEXT          (FK)

TBLJOBS
JOBID                              SHORT TEXT (PK)   eg XYZ-ABC
JOBDESCRIPTION             SHORT TEXT                  
WEIGHT                           DOUBLE

This is the SQL for querying these tables.

SELECT TBLHEADER.HEADERID, TBLHEADER.QDATE, TBLHEADER.JOBID
FROM TBLJOB INNER JOIN TBLHEADER ON TBLJOB.JOBID = TBLHEADER.JOBID;

If I run this query in access I can only enter a value in the JOBID field if it already exists in TBLJOBS. Which is what I want.

The other way just updates the value in TBLJOBS which is definitely what I don't want.

I guess I'm wondering if this autoincrement pk for the Jobs table is a good idea. Seems to make things a lot more complicated.

Answer 4

I guess I'm wondering if this autoincrement pk for the Jobs table is a good idea. Seems to make things a lot more complicated.

Hi Andy,

In my opinion this autoincrement pk for the Jobs is a good idea. It makes things a little more structured.

Imb.

Answer 5

Thanks for looing at this guys, I'll have to look at this again tomorrow as it's midnight now.

Cheers

Answer 6

Hi AndyNakamura,

please visit the links below for more information regarding table relationships.

Guide to table relationships

Regrads

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 7

Hi, This shows the relationships using a surrogate Primary in the tblJobs

and this shows the relations using a natural primary in tblJobs

The advantage of the natural key is that I can have a field in the headers table that is a lookup for the job field in the jobs table. In the surrogate method for tblJobs all the user will see is a list of integers.
To make this work I'd have to make another query that finds the JobId from this integer and display that to the user but that returns the primary key (integer) for actually selecting which value to save.
I'm just not used to doing it this way I guess.

Answer 8

Hi,
To show the results of a query on the surrogate method.

SELECT tblHeadersSurr.ID, tblHeadersSurr.QDATE, tblHeadersSurr.FK_TO_TBLJOBS
FROM tblJobSurr INNER JOIN tblHeadersSurr ON tblJobSurr.SURRID = tblHeadersSurr.FK_TO_TBLJOBS;

and on the natural method.

SELECT tblHeaders.ID, tblHeaders.QDATE, tblHeaders.JOBID
FROM tblJobNat INNER JOIN tblHeaders ON tblJobNat.JOBID = tblHeaders.JOBID;

I have to admit to being a fraud when it comes to SQL statements. I always have to get Access to write them for me except for the simplest ones.

Answer 9

It depends on the development approach and the requirements. But as always: use what you think is better in your situation.

Imho a key should be invariant. This is not always true. That's why we have those cascading updates (sigh.). So I prefer surrogate keys, when there is none.

But some constraints are pretty hard to implement using surrogate keys. Like constraints on hierarchies. Also using natural keys especially compound natural keys allows normally better indexing.

Answer 10

hmm, JOBID SHORT TEXT (PK) eg XYZ-ABC, it seems that you JOBID is not an atomic value..

Answer 11

Hi Stephan,
Thanks for your comments.
Excuse my ignorance. What do you mean by 'Atomic'?

Answer 12

The advantage of the natural key is that I can have a field in the headers table that is a lookup for the job field in the jobs table. In the surrogate method for tblJobs all the user will see is a list of integers.
To make this work I'd have to make another query that finds the JobId from this integer and display that to the user but that returns the primary key (integer) for actually selecting which value to save.
I'm just not used to doing it this way I guess.

Hi Andy,

In a lookup for the job you can make the surrogate key invisible, and the "natural name" (e.g. JobDescription) visible.

Imb.

Answer 13

Imho a key should be invariant. This is not always true. That's why we have those cascading updates (sigh.). So I prefer surrogate keys, when there is none.

Hi Stefan,

Interesting! Can you give an example?

I have never met a situation where the primary (surrogate) key had to be changed. A foreign key can, but that should not be a problem.

Imb.

Answer 14

The 1. Normal Form (1NF): Store only atomic values. Thus values which are no further splitable or if so, the information in those parts is not relevant to the database (application, user).

A JOBID having a format of XYZ-ABC indicates a first and a second part. These parts have often a valuable meaning in natural keys. Thus storing it this way is often an mistake, even when it does not manifest in errors.

Answer 15

Hi Andy,

In a lookup for the job you can make the surrogate key invisible, and the "natural name" (e.g. JobDescription) visible.

Imb.

Hi,
The lookup values have to be the natural name otherwise they can't be selected when entering data to the headers table. I don't know how to give it that functionality without more coding.

I suppose I could make a combobox and fill it with natural name values, then when the user selects one of these find its PK value and use that to update the headers table.

Seems to be making life quite a lot more difficult.

Answer 16

Surrogate keys don't change, cause they are invariant per se.

The last sentence above should read:

So I prefer surrogate keys, when there is no invariant natural key.

Cause natural keys are sometimes not stable, especially when they are not atomic. E.g. ticket ID's consisting of the ticket number and the ticket owner. When the ticket changed ownership, the ID changed the name abbreviation part.

Or product numbers..

Answer 17

I suppose I could make a combobox and fill it with natural name values, then when the user selects one of these find its PK value and use that to update the headers table.

Seems to be making life quite a lot more difficult.

Hi Andy,

You can make a combobox with its RowSource containing both the surrogate key field and the natural name field.

The surrogate key field is the connecting field and has a width of 0 (so invisible). The natural name field is what the users sees, and can select

It makes life a little different, but after you master it, it can make life a lot more easier.

Imb.

Answer 18

hmm, JOBID SHORT TEXT (PK) eg XYZ-ABC, it seems that you JOBID is not an atomic value..

Hi Stefan,
Maybe the hyphen is misleading. The value is just a string. It's not composite in any way.
It is usually just the customers part number or drawing number (Just called numbers they are usually alphanumeric).
They are actually invariant. As in invoice numbers are invariant. Once used they can't be changed otherwise historical records for invoices, QC, etc would become useless.
If a customer were to change his drawing numbering system then even so the old ones would have to remain.

Answer 19

Often there are number ranges involved..