Hi David,
In your database on the countries of the world think you were able to
mostly avoid using numeric keys however in this case, they will save you a
world of headaches.
My suggestion to you: Create one table per taxonomic level, starting at
Kingdom and working all of the way down to species. You may think about a
Hybrid or Varietal table to deal with animals like the mule. That table
would need two species IDs as its parent keys.
CREATE TABLE Kingdom (
ID smallint not null auto_increment primary key,
Name varchar(20),
key(name)
)
CREATE TABLE Phyllum (
ID smallint not null auto_increment,
Kingdom_ID int not null,
Name varchar(20),
primary Key (Kingdom_Id, Name),
Key(ID),
Key(Name)
)
CREATE TABLE Class (
ID int not null auto_increment,
Phyllum_ID int not null,
Name varchar(20),
primary Key (Phyllum_Id, Name),
Key(ID),
Key(Name)
)
(...continue building tables, following the pattern above..)
CREATE TABLE Species (
ID bigint not null auto_increment,
Genus_ID int not null,
Name varchar(45),
CommonName varchar(75)
primary Key (Phyllum_Id, Name),
Key(ID),
Key(Name)
)
With a structure like this, you will avoid the need to create any kind of
artificial text-number hybrid to avoid duplication of your parent-child
relationship keys. Also, since it is what the entire scientific community
uses, you should be able to find excellent documentation on it. It's a
well-worn and proven system of cataloging living things. No need for you
to reinvent the wheel.
I think that your URL scheme is flawed. Here is a "full" URL with numbers
substituted for your names:
full.geozoo.org/1/3/65/450/900/2300/657874
This URL lists each level's key from Kingdom to Species. What if Genus
2300 did not belong to Family 900? This would have been an invalid query
(and you could have TONS of those)
I think a better version would sound very "Borg"-ish
www.geozoo.org/?species=15379
Once you know the species, you can traverse the tree and collect
information as far up as you would like to go.
You asked for a "trick" to autogenerate your ID keys. Use an integer
column with auto_increment and avoid those hybrid keys.
You asked in a second thread about how to create compound keys. Look at
the primary key of my Phyllum table above. I list 2 columns. That's all
there is to making a compound key. Put them in the order you would use
them most often. By using both columns (parent_ID, child_name) to build
the primary key, I guarantee that each "parent_Id" - "child_name"
combination will appear only once per table.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
David Blomstrom <[EMAIL PROTECTED]> wrote on 07/17/2004 01:03:54
PM:
> I'm working on a big animals database and have a
> question that's a little hard to explain, though I
> suspect it will make sense to the pros.
>
> First, a little background. I'm tentatively setting up
> a parent-child relationship and putting all the basic
> mammal data in a separate table. Below are some sample
> rows to give you an idea of what I have in mind:
>
> ID NAME COMMON NAME PARENT
> car | Carnivora | meat eaters | Mammalia
> can | Canidae | dog family | Carnivora
> can | Canis | wolf & coyote | Canidae
> lup | lupus | wolf | Canis
>
> As you can see, lupus' parent is Canis, Canis' parent
> is Canidae and Canidae's parent is Carnivora.
>
> The three-letter ID works great in many respects. I
> want to use three-letter URL's, so instead of...
>
> mammals.geozoo.org/carnivora/canidae/canis/lupus/
>
> I could use...
>
> mammals.geozoo.org/car/can/can/lup/
>
> The obvious problem is that C-A-N are the first three
> letters of both Canidae and Canis, along with many
> other scientific names.
>
> So I came up with what I think is a clever solution -
> I can simply add numerals to make unique ID's, then
> use PHP to strip out the numerals when I display the
> data.
>
> I might number the orders, families, genera and
> species separately. This is what some codes might look
> like:
>
> Carnivora = car1
> Artiodactyla = art2
> lupus = lup3012
>
> So here's my question:
>
> Rather than sit down and write out codes for more than
> 14,000 species (nearly 5,000 birds and 9,000 birds
> alone), is there some trick I can use to generate
> these numerals with MySQL?
>
> For example, could I create a column of codes that
> look like this:
>
> art
> car
> pri
> cet
>
> then instruct MySQL to automatically add numerals
> sequentially, so it looks like this?:
>
> art1
> car2
> pri3
> cet4
>
> One possibility is to create a separate field filled
> with numerical values and place it next to my codes,
> like this:
>
> Field 1 Field 2
> art | 1
> car | 2
> tig | 1033
>
> Then I'd have to somehow join the two fields so that
> MySQL recognizes the values as...
>
> art1
> car2
> tig1033
>
> I've already learned how to strip out the numerals
> with PHP. But this project would be a lot easier if I
> could at least figure out how to generate the
> numerical suffixes with MySQL.
>
> Any tips?
>
> Thanks.
>
>
>
> __________________________________
> Do you Yahoo!?
> Vote for the stars of Yahoo!'s next ad campaign!
> http://advision.webevents.yahoo.com/yahoo/votelifeengine/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
