Categorizing
 

I'm in the process of making a website in php on Reptiles and Amphibians. Just using free hosting while I'm making it. Address is http://herp.awardspace.com.

At the moment, I can add animals to the database and upload articles and those are searchable using the search box near the top right hand side.

I have one problem though. How do I categorize the animals?

Lets say I want to find a Royal Python...I'd put it under Reptiles > Snakes > Non-Venemous > Pythons.

On the site, if you navigate through all those it will indeed show a royal python. But I need it to also show up in the higher categories before that specific one. eg. The royal python will show up with all boa constrictors when viewing the non venemous category.

As it stands I have a category field in each animal record where I put its lowest category, in this case pythons.

I have a category table: category(id, name, parent). Parent being the parent category of that category.

Obviously this is not the right/ best way to do it so if any of that makes sense and anyone has any ideas I'd be greatful to hear them.



Thanks.

Remove the category field from the animals table, and add in a linking table that connects categories to animals.

e.g.
category_animal(category_id, animal_id)

Ah, very good idea! Thank you very much!

So if I do it that way...what would be the best way to add the animal in all the categories?

Eg. If i add a royal python in pythons, code would look up pythons category and also add it in the parent category, and then also in the parent of that one too etc until the top category is reched?

You might want to do some research into relational database design, perhaps, specifically, entity-relationship modeling. I have little doubt that the effort would result in a higher level of satisfaction with your design.

A for loop with multiple insert statements. Presumably you're going to be reading from the database far more than writing to it, so a for-loop, though less efficient than a single insert query, seems the most efficient way overall.

You could set up a table to store all an animal's ancestor categories, I guess, something like:
ancestors(category_id, ancestor_id)

But that's just trading memory for CPU, and considering each animal is probably only going to belong to a relatively small group of categories, it's probably not worth it. Go with a for-loop and multiple inserts, would be my advice.

Thank you for everyone's help.

I took out the category field in the animal table. Created a link table category_animal(category id, animal id). And when adding a new animal it asks the user for its lowest category, (eg python and not just snakes), and it adds it in pythons, then looks at pythons parent and adds it there, and so on until it gets to a top level category!