List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 24 2005 8:52pm
Subject:Re: Treating Two Fields Like One
View as plain text  
David,

/<snip>
 >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, /

No. Existing autosequential (auto-increment) values are not affected by 
new autosequential values.

One argument for keeping each level in its own table is the possibility 
of cleanly enforcing referential integrity, eg a constraint that 
prevents deletion of a parent row which is referenced in a row of a 
child tabke.

/>messing up the values in
 >PARENTID./

No. See above.

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

Sorry, what do you mean by 'database table code'? What I see is a text 
representation of some rows from a table.

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

I gather yuou have some code that reads what the user types in and tries 
to complete her request sentence. For the cases where the last name she 
typed is the name of more than one entity, you need to provide the user 
an interface for making a choice. One possibility for the case you 
instance would be a dropdown miniwindow which collects the 
possibilities, here leo (Canis) or leo (Panthera), and puts them in a 
dropdown for her to choose. Another possibility is to put up a page or 
frame that represents the bit of the tree that the user has so far 
specified, plus one level, and let her walk down the tree.

/>Would it be possible to somehow combine my
 >auto-incrementing primary key with the field Name,
 >converting leo / leo to 5leo / 8leo?
/
Sure. You can query on anything you please, and show any part of that 
query, or all of it, or none of it.

/>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...
/
Sure. The user shouldn't have to be bothered with the details of how you 
make her job easier.

I do not understand why you think your autoincrement values need to 
change. The whole idea is that, once assigned, they _never_ change. If 
your update model entails changing primary keys, fix the model.

PB

-----

David Blomstrom wrote:

>--- Peter Brawley <peter.brawley@stripped> 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...
>
><a href="http://geozoo/stacks/leo/">leo</a>
>
>not this...
>
><a href="http://geozoo/stacks/8leo/">8leo</a>
>
>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 
>
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005
Thread
Treating Two Fields Like OneDavid Blomstrom24 Aug
  • Re: Treating Two Fields Like OneEnrique Sanchez Vela24 Aug
  • Re: Treating Two Fields Like Onedouglass_davis24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like Onedouglass_davis24 Aug
        • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
  • Re: Treating Two Fields Like OnePeter Brawley24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like OneSGreen24 Aug
        • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like OnePeter Brawley24 Aug
RE: Treating Two Fields Like OneGordon Bruce24 Aug
Re: Treating Two Fields Like OneDavid Blomstrom25 Aug
  • Re: Treating Two Fields Like Onedouglass_davis25 Aug