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.
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:
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
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?Code:
SELECT L_ID, L_Title FROM Links WHERE L_Cats LIKE '%,1,%'