View Full Version : using circular references

07-19-2006, 02:00 PM
The current database schema for my site has tables for each of 4 possible levels of 'category' and 'sub category', as well as link tables between each level, and, where applicable, link tables to the product table.

I want to simplify this messy structure into a 'group' table and 'group_link' table, and then the usual product link table.

This will required circular referencing, and that seems quite complicated to me. Is it, in general, a good idea? It seems that, while it's the simpler design, the queries would be more complex and maybe there could infinite loops or something. Note, I'm using mysql 4.1 so referential integrity is not enforced (so i understand)

Here's the table structure:

create table group(
groupID int primary key auto_increment,
groupName varchar(25)

create table group_link(
parentID int,
childID int

create table product_link(
groupID int,
productID int

create table product(
productID int primary key auto_increment,
productName varchar(50)

So to get details of a group and the products in a the group:

SELECT g.groupName, p.productName
FROM group g
JOIN product_link pl ON g.groupID=pl.groupID
JOIN products p ON pl.productID=p.productID
WHERE g.groupID=25

Fine, that's basic stuff. But how about getting a list of child groups for a particular group?

SELECT g1.groupName as parentName, g2,groupName as childName
FROM group g1
JOIN group_link gl ON g1.groupID=gl.parentID
JOIN group g2 ON gl.childID=g2.groupID

Is there a better way to do this? how about if I wanted it to loop through and find all the child groups for all levels under a particular group? And how about if that group itself was listed as a child of itself? Would this be bad & how do i stop it? Also how would I get a list of all products in all groups under a particular group?

Thanks for reading this far...

07-19-2006, 02:51 PM
one table with a productid, category and subcategory is likely what you need.

You might also want to check out this article (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html) on hierarchical data.

07-19-2006, 04:03 PM
That was a really good article-- thanks for the link!