Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts

    using circular references

    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:
    Code:
    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:
    Code:
    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?
    Code:
    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...

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    one table with a productid, category and subcategory is likely what you need.

    You might also want to check out this article on hierarchical data.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That was a really good article-- thanks for the link!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •