On 3/8/2011 4:24 PM, Darren New wrote:
> 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.
>

I have to agree with Darren - A primary key is not necessarily an 
auto-generated value (random or auto-increment).  It only has to be 
unique and not change.

For me, the *easiest* way to guarantee no change is to have an 
auto-generated integer.  Can it be done better, yes.  But without the 
experience of how to look at data, I run too high of a chance that I 
chose the wrong key and it needs changed.

>> 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 see the point in having the key have more meaning.  It reduces the 
mental referencing that must go on to understand the data without a 
proper interface.


>> 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.
>


I'm on this page fully.  I prefer to stay away from MS database 
solutions.  Not that they are not good, but they are just too ... well 
just 'too'.

Post a reply to this message