From: David Blomstrom Date: August 24 2005 7:31pm Subject: Re: Treating Two Fields Like One List-Archive: http://lists.mysql.com/mysql/188258 Message-Id: <20050824193119.51265.qmail@web60420.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit --- Peter Brawley wrote: "As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia > Carnivora > Canidae > Canis > leo, rather than the lion, Mammalia > Carnivora > Felidae > Panthera > leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... leo not this... 8leo 2. The numerals would have to be fluid, as I will be adding and deleting rows. Thus, the lion could be 8leo one day and 9leo the next. I can take this to a PHP forum to learn how to implement it. But I thought someone on this forum might tell me if it can be done in the first place. Thanks. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com