On the PHP forum, I asked about a key for my mammals table. I want it to feature the first three letters of each taxonomic name. For example the key for Carnivora would be car, while Felidae would be fel. The only problem is that there are thousands of scientific names, many of which feature the same first three letters.
Someone suggested I used a composite index, which sounds like a great idea. I have another field which is simply a column of numerals, about 4,800 for the mammals table. Here are examples of some of the codes that might be generated by using both columns:
art1
xen38
deu3800
That will give me unique codes, and I can then strip the numerals out for display purposes. But I can’t figure out how to make a composite index. I’ve been looking at MySQL’s website and some tutorials but haven’t found clear instructions yet.
I know how to create an index in phpMyAdmin. Would I simply create an index on one field, create an index on a second field, then somehow join them into a composite index with phpMyAdmin? And do the fields have to be physically adjacent, or could I create a composite index a table’s first and last fields?
can you explain what you mean by “i want it [the key] to feature the first three letters of each taxonomic name”
what do you mean by “feature”?
i would just declare an index on the name column
the purpose of an index is to find rows efficiently, so if you have an index on the name field, you can then do stuff like WHERE name LIKE ‘fel%’ and the query will be executed using the index on the name
I have a table with a field named FamMamName that includes the name of every mammal order, family, subfamily, genus and species. I just made a second field that features the first three letters of each of these scientific names. Here are some examples:
I would like to designate the field with the three-letter codes a key, but there are many duplicate entries. For example, Balaenidae and Balaenopteridae (two whale familes) both yield “bal.”
Unfortunately, the names aren’t unique, either. I can’t think of an example offhand, but here’s a hypothetical example:
Canis blanca (the white-haired dog)
Cervus blanca (the white-haired deer)
The genus names are unique, but both species names are “blanca.”
So my scheme won’t work unless I can somehow modify either the names or their three-letter abbreviations so that each one is unique.
I just figured out how to display the three-letter codes, and I put a numerical field right next to it. I can now put it in Dreamweaver and play with the HTML, transforming it into a single column.
So I would start with three fields, like this:
car | 2 | Carnivora
pri | 12 | Primates
bla | 183 | blanca
bla | 4922 | blanca
But if I can figure out how to make a composite key, I could leave the three-letter codes and numerals in separate columns. It would make it much easier to add or remove species, and it might also be handy if I want to display data from one column or another.
I was hoping you could make a composite key with phpMyAdmin, because I haven’t yet learned how to work with MySQL code. I’ll take a look at another program I have, Navcat. It’s proven itself pretty handy.
okay, i understand what you’re trying to do, but the answer that i come up with is simply to store the URL path for each animal
that way, you can fool around with (i.e. change) the URL scheme all you want, and not have to try to mimic the structure of the URL scheme in the database
but hey, what do i know
by the way, there’s no problem declaring an index on a column that has many duplicates
note: the idea i’m trying to give you is that you don’t hardcode the navigation structure in your html, and then try to make the database match it, rather, you store the url for anything as a data value for that thing
that way you can change the url scheme and not have to recode all your pages
