I posted the following to Stack Exhange yesterday and got a -1 rating and no replies for my troubles, so plainly it was inappropriate. Can anyone on this board, particularly anyone with experience of designing repositories with multi-level taxonomies, point me towards example designs? Although my Q is initially about a personal site, I realised quickly that it's also applicable to repositories implementing taxonomies, and of course many large-scale repos (eg Intralibrary, DSpace) do exactly that.

---------

I'm looking to implement a small static site of mine (CALL@Hull) in a relational database. The site already has a shallow hierarchical data structure (taxonomy), to which I might want to add another hierarchy or two. I've looked at the thread "Creating Taxonomy table in MySQL" from a few years back, which opened my eyes up to the concepts of Adjacency Lists and Nested Set models, outlined in some detail in the the article Managing Hierarchical Data in MySQL. Another useful thread is "What are the options for storing hierarchical data in a relational database?".

My question: is there an example hierarchical database application/diagram which I could study to get my head around storing taxonomies in a relational DB?

I'll be implementing in PHP (Codeigniter) and MySQL, but I'm really after seeing the database and application structure at a design level. If this doesn't count as a programming or DB design question then my apologies for posting in the wrong place.

I'm also into (educational) metadata schemas and learning object repositories, so it would be useful for me to understand more general solutions to this topic. I'm looking at small-scale databases (eg <10k records) so am not bothered about execution times for different approaches.

-----