List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 24 2005 8:22pm
Subject:Re: Treating Two Fields Like One
View as plain text  
David Blomstrom <david_blomstrom@stripped> wrote on 08/24/2005 03:31:19 
PM:

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

You have confused front end representation with back-end data design. How 
you store your data and create your data relationships is only marginally 
related to what your code makes it look like when it presents your data 
for the user. Why just create longer unique path names that represent the 
actual taxonomic path you have to take to get to an animal? What's wrong 
with:

http://geozoo/stacks/mamalia/carnivora/canidae/canis/leo/
http://geozoo/stacks/mamalia/carnivora/felidae/panthera/leo/

Those are unique and they accurately model your heirarchy. It's a good 
learning tool too as it documents the actual lineage of an animal in its 
URL. Each level of the path could resolve to something distinctive about 
each taxonomic group

http://geozoo/stacks/mamalia/carnivora/canidae/ would take you to a page 
about the dog family
http://geozoo/stacks/mamalia/carnivora/ would take you to a page about the 
order of carnivores
... and so on....

You don't have to worry about reorganziation of your taxonomic tree 
breaking your links (like you were when you wanted to link based on ID). 
You can add and delete nodes in your taxonomy at will (a deleted node 
could show some default "sorry, no profile exists yet. This site is still 
under construction" - type message).

What's wrong with returning two responses if someone searched on the 
species "leo". What if they didn't know about Canis leo? That could be a 
cool surprise. Let the user figure out which one they really wanted to 
see. That means that with your existing tree, you have to search the tree 
for leo and if there is only one match you show the matching page, 
otherwise you have to present a page that shows them the options 
available.

Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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