Primary Key Field Best Practices 2

[Michael42]

Hello,

In an Oracle9i database running on Sun Solaris 8 I am having a tuff time determining the best field for a Primary Key on a very large table. At the moment a "natural key" is being used where it consists of the fields: date_time, latitude and longitude.

I have always thought a PrimaryKey should be bound to a meaningless ID field - like off a sequence etc.

1. In your view what are the Pros and Cons of using this natural key as opposed to a sequence or meaningless field?

2. Can you recomemend any best practices on creation of Primary Key fields with ideal performance as the goal?

Thanks for your advice,

Michael42

 

[SantaMufasa]

Michael,

Your intuition is certainly on the right track. There are Seven Rules for Well-Behaved Primary Keys:

1) Universal: Every row has a value in that column; not null.

2) Unique: No two rows can share the same value.

3) Short as possible while preserving uniqueness: The shorter the possible, the less space it takes to store in the PK column, then the savings are multiplied by each of the foreign key (FK) references.

4) Numeric: Numeric values take roughly half the storage space as beyond-numeric values. Numeric, by definition means shorter, thus supporting Rule #3.

5) Not subject to change: If a PK value must change, then all of the FK references must change, as well. Avoidable change is a processing waste.

6) "Ours": You do not want to use someone else's PK scheme...If their scheme changes, so must your scheme, thus breaking Rule #5.

7) "Stupid": Good Primary Keys should not be intelligent. Specifically, they should not contain intelligence or have any special meaning...Meanings can change, therefore again breaking Rule #5.

Let's now apply these rules against your "natural key"...Which rule(s) does a natural key break?:

Since you have not disclosed what the DATE_TIME, LONGITUDE, and LATITUDE represent, it is difficult for me to assess with certainty which rules your "natural key" breaks, but I'll make some assertions which you can apply or reject:

Rule 1: Presumably each row can have a DATE_TIME, LON/LAT attribute.

Rule 2: If the "natural key" applies to some object's location and a date_time attribute, depending upon the objects' sizes, is it possible that two objects could be in the same place at the same date_time? If so, it breaks Rule 2.

Rule 3: Certainly your "natural key" breaks Rule 3...It is longer/takes more space that it needs to to preserve uniqueness: Oracle DATE_TIME columns require, at minimum, 7 bytes; Longitude and Latitude, depending upon storage semantic, require, at minimum 7 digits each...All totalled those are 14 digits plus 7 bytes which would store the equivalent of a 28-digit number in Oracle. 28 digits would uniquely identify 9,999,999,999,999,999,999,999,999,999 (10 Octillion minus 1). I'll bet you won't have that many rows of data.

Rule 4: If you have a DATE_TIME column concatenated with two other numeric columns, then you do not have a simple NUMERIC column, thus breaking Rule 4.

Rule 5: If the objects for which you are recording DATE_TIME, LON/LAT can change locations, then they are going to break Rule 5.

Rule 6: Since there are multiple schemes for representing both DATE_TIME and Longitude and Latitude, someone can make a business decision to change schemes, thus being subject to outside influences (out of our control), thus potentially breaking Rule 6.

Rule 7: Your "natural key" is certainly not "stupid"...There is a ton of intelligent embedded therein, thus breaking Rule 7.

So, of the Seven Rules for Well-Behaved Primary Keys:
your "natural key" may break six of the rules. There is no good case that I can think of for using a "natural key" as a Primary Key since, by definition, it breaks so many of the Seven Rules....

Someone may argue, "Well, we're already using the space to store DATE_TIME and LON/LAT...Let's just leverage the space as a Primary Key." But what they may save by not allocating a few bytes to store a simple number-generator value, they lose virtually immediately by having to use all the extra wasted space in each and every foreign key using a longer-than-necessary value to point back to its "natural" Primary Key.

Just say "No!" to "natural keys" as PKs.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

SantaMufasa,

Thanks for such a detailed response.

Michael42