David- generate a composite index on a unique combination such as genus/species Regards Martin ----- Original Message ----- From: "David Blomstrom" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 17, 2004 1:03 PM Subject: Animals Database Questions
> 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
