Many-to-Many Hierarchy with Multiple Parents - PHP, MySQL

I'm trying to create a list of technology books by category, where each book can belong to more than one category, and each category can be both a parent category and a sub-category.

Here's an illustration:

JavaScript

JavaScript Patterns

Object-Oriented JavaScript

• Ajax

  Ajax Definitive Guide

  Bulletproof Ajax

• jQuery

  Learning jQuery 1.3

  PHP jQuery Cookbook

PHP

PHP in a Nutshell

PHP jQuery Cookbook

Ajax

Ajax Definitive Guide

Bulletproof Ajax

• XML

  XML Hacks

  No-Nonsense XML

--

As you can see...

• The book "PHP jQuery Cookbook" belongs开发者_StackOverflow to two categories: PHP and jQuery
• The category "Ajax" is both a child of JavaScript and a parent of XML (but XML isn't a child of JavaScript)

I've designed the database tables this way:

BOOK:          book_id, book_title  
CATEGORY:      category_id, category_name  
BOOK_CATEGORY: book_id, category_id
CATEGORY_TREE: parent_category_id, child_category_id

I've read many other questions/answers on hierarchical data in MySQL, but nothing that can handle this type of "loose" hierarchy.

Does anyone know how to set up a list in this way?

---

Assuming your categories cannot form cycles, like a->b->c->a, your structure is called directed acyclic graph, which is not easy to handle in SQL, but possible. Googling that should give some results, you can also start here: http://www.codeproject.com/KB/database/Modeling_DAGs_on_SQL_DBs.aspx

---

If your dataset is small (<10000) then you could just fetch all the data in 4 SELECT all queries and do all the category/subcategory computation in PHP.

Trees and relational databases don't go together very well :)

---

Do it all with php, create a function which you include into pages, that way if anything ever changes you don't have to update the table, just the file which includes the function.

---

I would create

books (book_id, category_id)
categories (category_id, parent_category_id, category_name, category_level)

where category.parent_category_id can be NULL. If it is NULL, then category_level will be 0 (of 1, whatever you want).