[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [ProgSoc] NULL values in primary keys in MySQL



On Sunday 04 June 2006 1:20 pm, Myles Byrne wrote:
 ] On 04/06/2006, at 12:57 AM, John Elliot wrote:
 ] > Natural keys are a nightmare.
 ] 
 ] I just want to reaffirm this. Unless this is a legacy db where it's  

 Can you point me to a nice explanation of why this is so?

 I have an easier time conceptualising a natural key, than an
 arbitrary one.  Most (all?) of my selects, joins, and sorts will be
 based upon the two attributes that go to make up my (natural)
 primary key. 

 I'm not talking a lot of data - maybe in the hundreds of rows, with
 about two dozen attributes (no large blobs).  The other four or
 five tables will be comparably trivial.  Concurrent users would be
 maybe a dozen.  This is not a high performance OLTP app I'm cranking
 out.  I haven't even looked at indexes, as I imagine they'd be
 overkill, plus I don't fully understand their size/perf  implications.

 Is the avoidance of natural keys for performance reasons, or the
 tendency for them to not be immutable?  (See - I did go and read
 up on all this stuff last night.)

 Just what does happen if I change my '0' attribute on one of these
 records to, say, a 'j' in the future.  Will that really break things,
 or is that only a concern with far more complex schemas that have
 lots of permanent connections between tables?

 I'm loathe to add another attribute, particularly one that I won't
 use, and that has no *meaning* -- yes, this may be an old-fashioned
 way of thinking about schema design.  Happy to be dragged into the
 21st century.

 arp the rested -- I realised that I'd been using the phrase primary
 key to refer to what is in fact a compound key.  I'd have data that
 looks like the following.  PK defined as combo of (number, suffix).

 number, suffix,  other crap ...
 1, NULL,
 2, a,
 2, b, 
 3, g
 3, j
 4, NULL
 5, NULL
 etc ... 

 There would never be a row of data that wasn't uniquely identified
 via a unique combination of INT and one of [ NULL, [a-z]].

 This comes back to John's observations .. and I still don't grok
 it.  I realise that NULL != NULL, but I'm not comparing one NULL
 to another.  MySQL (at least) gives the client the ability to do a
 test such as 'WHERE suffix = NULL' -- so presumably the engine has
 a way of understanding that something *is* null, at least, and that's
 as effective (for my purposes) as its ability to determine if an
 attribute is a zero or a particular lower-case char.

 I'm happy to let this one go .. I suspect it's a lack of understanding
 of the subtleties and nuances of what NULL really means.

 Jedd.

-
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to progsoc-request@xxxxxxxxxxxxxxxxxxx
If you are having trouble, ask owner-progsoc@xxxxxxxxxxxxxxxxxx for help.