From: Peter Brawley
Date: April 17 2005 5:53am
Subject: Re: Relative Numeric Values
List-Archive: http://lists.mysql.com/mysql/182748
Message-Id: <4261F9D5.8020001@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

David,

I agree with Kim. Trying to encode information into primary keys is a 
bit like trying to write a legal contract in chalk on a sidewalk. The 
contract is washed away next time it rains, the neat looking key order 
is washed away by errors, species discoveries & reclassifications. The 
PK doesn't need to mean anything, indeed it shouldn't be made vulnerable 
to real-world errors of any kind; it's job is strictly logical, as a 
marker that guarantees row uniqueness and can relate rows in one table 
to rows in another..

HTH.

PB

-----

David Blomstrom wrote:

>I think my question is more oriented towards PHP, but
>I'd like to ask it on this list, as I suspect the
>solution may involve MySQL.
>
>I'm about to start developing an enormous database
>focusing on the animal kingdom and want to find a key
>system more user friendly than the traditional
>scientific name.
>
>So imagine instead a page with the following in the
>head section:
>
>$AnimalID = 'canlup';
>
>This page displays information on the wolf, based on
>the first three letters of its genus and species name,
>Canis lupus.
>
>Now imagine a page with this value:
>
>$AnimalID = 'bal';
>
>This page displays information on the whale family
>Balaenidae. But what about the whale family
>Balaenopteridae, which begins with the same three
>letters?
>
>I could solve this problem by adding a numerical key
>to my database and displaying the following:
>
>$AnimalID = 'bal23';
>$AnimalID = 'bal24';
>
>The problem with this is that it makes it much harder
>to work with my data. When tweaking a page or writing
>a script, I can easily remember that bal = Balaenidae,
>but I can't possibly remember which numeral is
>associated with each mammal family. Also, what happens
>if I add or subtract rows from my database table, and
>the above values suddenly change to bal27 and bal28?
>
>So here's what I think I'd like to do:
>
>$AnimalID = 'canlup1';
>$AnimalID = 'bal1';
>$AnimalID = 'bal2';
>
>The page with canlup1 will display the FIRST (and
>only) instance of canlup in the database - the wolf.
>
>The page with bal1 will display the first instance of
>bal, which will always be Balaenidae, whether the
>absolute value is bal27 or bal2884. A page with bal2
>will always display the next mammal family that begins
>with bal, Balaenopteridae.
>
>So I THINK all I need to do is create a variable that
>reflects a particular value's ordinal position in a
>database...
>abc1
>abc2
>abc3, etc.
>
>Plus, I'll have to join two or three fields together
>to form a key; e.g. animals.species + animals.numerals
>
>Does anyone know how I can do this? Thanks.
>
>
>		
>__________________________________ 
>Do you Yahoo!? 
>Plan great trips with Yahoo! Travel: Now over 17,000 guides!
>http://travel.yahoo.com/p-travelguide
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.13 - Release Date: 4/16/2005