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,
create table group_link(
create table product_link(
create table product(
productID int primary key auto_increment,
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
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...