Orchid XP v8 wrote:
> Pro-tip: Don't use real-world things as primary keys. Because you know 
> what? Real-world things have this nasty habit of *changing*. You don't 
> ever want your primary key to change. This is why databases up and down 
> the country refer to Mr Smith not as Mr Smith but as customer #238523.

I disagree. The only time you should use automatically-generated primary 
keys is when there is no attribute of the item in the table that is 
guaranteed not to change.

A sales receipt whose primary key is the cash register identifier and the 
time to the second when the receipt was printed is an excellent PK for a 
sales receipt. A record recording what song was playing on the radio has the 
obvious PK of location+timestamp+frequency. A road intersection has a fine 
PK of the GPS coordinates of the intersection.

The problem is that people are changing all the time, so it's very hard to 
have a customer record that persists with a PK that makes sense.

> Use randomly-generated integers with no real-world meaning as PKs and 
> you'll have fewer problems if/when the thing the key refers to changes 
> in some way.

The drawback of this is if you get a reference to the wrong value, you'll 
never notice.

> I would suggest not setting up an Oracle database, for example. It 
> sounds like it would be utter overkill here.

Yes. One of the free databases (Postgresql or mysql) or (on windows) SQL 
Server (Express, perhaps) would do fine.  You'd probably find the most free 
help with MySql, but it's kind of messy to run on Windows, or at least was 
last I checked. SQL Server Express would *probably* have enough performance 
for you, given you were using Access.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message