SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Jujubee's Avatar
    Join Date
    Mar 2001
    Location
    Canada
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I design "Related Topics" into my DB?

    I want a db of products with categories. Each product category would have "Related Categories".

    So for example, the "CD" category would have "cassettes", "LPs", "8-track" as the Related Categories.

    I'm not sure how to go about designing this?

    I have one table with the categories.

    CATID
    NAME


    So this is a many-to-many relationship but "self-referencing" (if that's the right term) because one row will be related to many other rows in the same table.

    Basically, my head is spinning.

    Thanks for the help.
    Last edited by Jujubee; Jul 2, 2001 at 20:57.

  2. #2
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    what database are you using?

    The easiest way would be is have another table with the ID's of your categories, so it would somthing like.

    Parent | Child
    1 | 5
    1 | 17
    2 | 54
    3 | 17


    etc etc etc.

    You can set up relationships or just use joins in your SQL statment.

    Hope this has helped
    .NET Code Monkey

  3. #3
    SitePoint Enthusiast Jujubee's Avatar
    Join Date
    Mar 2001
    Location
    Canada
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, ok, I know about linking tables, but would it be the ideal solution in this case?

    This would be fine with one parent and multiple childs, but each category would be related to a few other categories.

    ex:

    CD related to cassette, LP, 8-track
    cassette related to CD, LP, 8-track,
    LP related to.... etc

    There are tons of permutations. Wouldn't a plain linking table like you mention produce lots of redundant data?

    Or maybe I just don't understand what you mean.

    (Thanks for the reply. )
    Last edited by Jujubee; Jul 8, 2001 at 22:54.

  4. #4
    SitePoint Enthusiast Jujubee's Avatar
    Join Date
    Mar 2001
    Location
    Canada
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone else have any other ideas on how to "relate" rows in the same table efficiently?

    Thanks.

  5. #5
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in this case i dont think youll have to worry to much about efficientcy. i mean, we arent talking about a very big table so it doesnt realy make to much sense to me to even bother linking tables or anything.

    lets say you have a table with three columns: ID, Cat, and RelCat.

    ID could just be an auto number column with no actualy meaning and you could use it for your primary key. I believe with some databases you wouldnt even need this column but for something like ms access you would and i dont think it hurts to have it anyways.

    Cat would be the catagory and RelCat would be the catagory you want to relate to. so your table might look something like this:
    ID Cat RelCat
    1 CD LP
    2 CD 8-track
    3 CD cassette
    4 LP CD
    ect...
    one thing i notice about this example right away is that there is a patern to it. which means that, if the patterns like this exist throughout all your catagories and related catagories, then a grouping meathod would probly work best.

    Example: you have the catagories: Knives, Spoons, Cds, LPs, BarfBags and MoistTowlettes.

    there are three obvious groups here, right? you wouldnt want to relate BarfBags to LPs, would you?

    so in that case having a groupid column replace the RelCat column in the above example would be a much better. and you can give the groups names or just numbers; whatever you like.


    anyways, those are just a few ideas. you realy havent given enough info for me to tell you exactly what you should do so i guess ill leave it up to you to figure out what is best from here.

  6. #6
    SitePoint Enthusiast Jujubee's Avatar
    Join Date
    Mar 2001
    Location
    Canada
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Darkeye, thanks for the ideas.

    I just got done reading about database normalization and your first suggestion breaks a big rule. The redundant data will cause problems later if you want to make any changes to your categories. For example, if you want to change "CD" to "CDs" you'd have to find all the "CD" entries and change them. No big deal with only a few categories, but it's something I want to avoid.

    I really like your second idea though! I don't know why I didn't think of it . Makes sense to me and I think I'll add a "Secondary Group ID" for items that may belong to 2 groups. Thanks!

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    What you actually should be doing is using the CatID to relate, not the name. Your table will be much smaller and more efficient. IE, your table should have it like this (first line is the fieldnames:

    CatID, CatName, RelatedCat1, RelatedCat2
    1, 'CD', 2, 3
    2, 'LP', 3, 1
    3, 'Cassette', 1, 2

    Ideally, however, you'd actually have 2 tables:
    Table CatType (key: CatID):
    Fields: CatID, CatName
    1, CD
    2, LP
    3, Cassette

    Table CatRelation (Key: CatID + RelatedCat):
    Fields: CatID, RelatedCat
    1, 2
    1, 3

    You could then do this type of select (I'm going to code it in ASP since that's easiest for me...)

    Code:
    strSql = "SELECT CT.CatID, CatName FROM CatType CT ORDER by CT.CatID"
    set rsCatLoop =  my_Conn.Execute (strSql)
    if rsCatLoop.EOF or rsCatLoop.BOF then
       ' No Records Found -- error?
    else
        Response.Write "Cat ID, CatName, RelatedCat, RelatedCat, etc..." & VbCrLf
        do until rsCatLoop.EOF
            OutputLine = ""
            OutputLine = rsCatLoop("CatID") & ", " & rsCatLoop("CatName")
            strSql = " SELECT CatName"
            strSql = strSql & " FROM CatRelation CR, Type CT"
            strSql = strSql & " WHERE (CR.CatID = " rsCatLoop("CatID")
            strSql = strSql & " AND CR.RelatedCat = CT.CatID)" 
            strSql = strSql & " OR (CR.RelatedCat = " rsCatLoop("CatID")
            strSql = strSql & " AND CR.CatID = CT.CatID)" 
            set rsRelatedLoop = my_Conn.execute(strSql)
            if rsRelatedLoop.EOF or rsRelatedLoop.BOF then
               ' No Records Found - Do nothing
            else
                do until rsRelatedLoop.EOF
                    OutputLine = OutputLine & ", " & rsRelatedLoop("CatName")
                    rsRelatedLoop.movenext
                loop
            end if
            set rsRelatedLoop = nothing 
            Response.Write OutputLine & VbCrLf
            rsCatLoop.MoveNext
        Loop
    end if
    set rsCatLoop = nothing
    Basically, it selects all the Categories from the CatType table and all the related categories for that category. This is the least redundant and most flexible method to accomplish what you want, at least if I understand you properly.....

    You could also provide all the categories with a record for each related category in the related category table (ie if Cat 1 is related to 2, you'd have one record 1,2 and one record 2,1). This method means there are more record in the CatRelation table but would be much easier to read and also allow for one way relations. The code would be exactly the same except for the inner SQL statement which would then read:

    Code:
            strSql = " SELECT CatName"
            strSql = strSql & " FROM CatRelation CR, Type CT"
            strSql = strSql & " WHERE CR.CatID = " rsCatLoop("CatID")
            strSql = strSql & " AND CR.RelatedCat = CT.CatID"
    Hope this helps....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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