Many-to-many multi-parent hierarchy - PHP, MySQL

Question

I am trying to create a list of books by category by category, where each book can belong to more than one category, and each category can be either a parent or a subcategory.

Here is an illustration:

Javascript
JavaScript Templates
Object oriented javascript

• Ajax
  The Complete Ajax Guide
  Bulletproof Ajax

• JQuery
  Learning jQuery 1.3
  PHP jQuery Cookbook

Php
PHP in a Nutshell PHP jQuery Cookbook

Ajax
The Complete Ajax Guide
Bulletproof Ajax

• XML
  XML hacks
  No XML Reliability

-

As you can see...

• The book "PHP jQuery Cookbook" falls into two categories: PHP and jQuery
• The Ajax category is a child of JavaScript and a parent of XML (but XML is not a child of JavaScript).

I designed the database tables as follows:

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 read many other questions / answers on hierarchical data in MySQL, but nothing that can handle this type of “free” hierarchy.

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

Answer 1

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

Answer 2

If your data set is small (<10000), you can just get all the data in 4 SELECT of all queries and do all the category / subcategory calculations in PHP.

Trees and relational databases do not mix well :)

Answer 3

Do everything with php, create a function that you include in the pages, so if something changes, you do not need to update the table, just a file that includes the function.

Answer 4

I would create

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

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