David,
>1. Some species names are shared by more than one
>mammal. For example, there's a marsupial named
>Antechinus leo.
>2. Species are more properly cited with the name of
>their parent (genus), so I eventually want my species
>URL's to look like this:
<snip>
As you note, the names aren't guaranteed to be unique, or to stay the
same either, therefore they won't do as primary keys, therefore they
won't do as foreign keys. Also our understanding of these taxonomic
relationships can change even when the names don't, and it's considered
a design error to make it necessary to edit primary keys in order to
update database tables.
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.
It is indeed confusing to try to work out user interface and database
design issues at the same time. The usual suggestion is to solve the
database structure problem before you lay a hand on the UI problem. The
idea is to write a query or query template for every question yoiur
system may ever be asked, and build the tables that those queries need.
PB
David Blomstrom wrote:
I'm trying to make my first content management system
and am wrestling with a problem that seems to be about
equal parts PHP, Apache mod_rewrite and MySQL. I
wondered if anyone on this list can suggest a MySQL
solution - or partial solution.
I'm dealing with a single database table named
gzanimals that lists animal taxons (orders, families,
species, etc.) in a child-parent relationship (with
fields named "Name" and "Parent". For example:
NAME | PARENT
Animalia | (NULL)
Mammalia | Animalia
Carnivora | Mammalia
Felidae | Carnivora
Panthera | Felidae
leo | Panthera
The code above illustrates the taxonomic hierarchy
linking the lion (Panthera leo) to the animal kingdom
(Animalia). With Apache mod_rewrite, my URL's look
like this:
www.geozoo.org/stacks/Animalia
www.geozoo.org/stacks/Mammalia
www.geozoo.org/stacks/Carnivora
www.geozoo.org/stacks/Felidae
www.geozoo.org/stacks/Panthera
www.geozoo.org/stacks/leo
There are two problems with this strategy:
1. Some species names are shared by more than one
mammal. For example, there's a marsupial named
Antechinus leo.
2. Species are more properly cited with the name of
their parent (genus), so I eventually want my species
URL's to look like this:
www.geozoo.org/stacks/Panthera_leo
* * * * *
I can manipulate my PHP script and mod_rewrite to draw
from two fields and display Panthera_leo instead of
leo.
The problem is that I more or less lose contact with
my database; it recognizes leo as a row identifier,
while Panthera_leo is an unknown.
* * * * *
You can see a good example of what I'm trying to do at
http://www.geozoo.org/stacks/Carnivora Two separate
scripts drive the bread crumbs-style links at the top
of the page and the list of "children" (carnivore
families, in this case) in the column on the right.
If you click Felidae, the children change to a list of
genera that belong to the family Felidae. Click
Panthera - http://www.geozoo.org/stacks/Panthera - and
the children change to species that belong to the
genus Panthera.
As you can see, I've modified these so they display
the genus (parent) + species (child) name, both
physically and in the link. However, they are
nonfunctioning, as I haven't yet figured out how to to
deal with the space between the two values.
* * * * *
At this point, I'm thoroughly confused, especially
since fixes often require tweakingn two or more things
simultaneously - PHP, mod_rewrite and MySQL. But one
thing I haven't experimented with yet is combined
fields.
I created a key on two fields - Name and Parent - and
it NameDual. Is there a way to connect with NameDual
in a PHP script, or is it something that only exists
in my database?
For example, I thought I might change...
$taxon = mysql_query ("SELECT Name, NameCommon, Parent
FROM gzanimals AS GZA
WHERE Parent = '$_GET[taxon]'");
to...
$taxon = mysql_query ("SELECT NameDual, Name,
NameCommon, Parent FROM gzanimals AS GZA
WHERE Parent = '$_GET[taxon]'");
...except it didn't work.
Is there a way to do this, or can you see any other
MySQL solutions to my problem?
Thanks.
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.14/79 - Release Date: 8/22/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
