SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Multiple Category System

    I have a category system where an item can be in an unlimited number of categories. Instead of making a third table to link the two tables together, I decided to try using a string of category IDs separated by commas. My table layouts are similar to the example below.

    Code:
    Cats - C_ID | C_Title
    1 | Test 1
    2 | Test 2
    3 | Test 3
    4 | Test 4
    
    Links - L_ID | L_Title | L_Cats
    1 | Test 1 | 1, 2, 4
    2 | Test 2 | 2, 3
    3 | Test 3 | 1, 4
    The problem I found though is SELECTing links by category. I ended up having to wrap each number in commas and use a broad LIKE statement to pull it off.

    Code:
    SELECT L_ID, L_Title FROM Links WHERE L_Cats LIKE '%,1,%'
    I do not think this is the best way to go. Is there a better way or is this just a bad system to use?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the best practice method is a relationship or linking table

    Cats
    C_ID | C_Title
    1 | Test 1
    2 | Test 2
    3 | Test 3
    4 | Test 4

    Links
    L_ID | L_Title
    1 | Test 1
    2 | Test 2
    3 | Test 3

    LinkCats
    L_ID | L_Cat
    1 | 1
    1 | 2
    1 | 4
    2 | 2
    2 | 3
    3 | 1
    3 | 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    the best practice method is a relationship or linking table.
    Yeah, just as I figured.


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
  •