0

I want to have a picture gallery and each gallery will be marked with a set of tags, I want users to be able to search for any number of these tags and I'd like if it were somewhat efficient. I don't really know where to begin.

Each category has an entry in the 'categories' table, something like this:

id | name | description
---+------+------------------------------------
0  | cats | This is a gallery full of cats
1  | dogs | This is a gallery full of dogs

and each entry in the 'galleries' table would have multiple category IDs stored for each category they belong in (though I'm not sure how I'd store them or query them at this time)

If it weren't for the search function I planned on I'd just serialize the category array, but that's not efficient to query at all.

puromonogatari
  • 378
  • 3
  • 10

1 Answers1

1

go for third normal form

 gallery 
 id | desc
----+---------
1   | dogs
2   | cats
3   | cats and dogs playing

.

gallery_category
gallery_id | category_id 
-----------+-------------
 1         | 1
 2         | 0
 3         | 0
 3         | 1

then use union intersect

 select gallery_id from gallery_category where category_id=1
 INTERSECT 
 select gallery_id from gallery_category where category_id=0

with indices on both columns of gallery_category this should be reasonably fast.

unfortunately mysql doesn't do intersect, but you can get the same effect with join.

select a.gallery_id 
  from  gallery_category as a 
  join  gallery_category as b on a.gallery_id = b.gallery_id
  where a.category_id=1 and b.category_id=0
user340140
  • 628
  • 6
  • 10
  • the union doesn't make sense, it would be better to just use IN: `SELECT gallery_id FROM gallery_category WHERE category_ID IN (0,1);` that would return the exact same results as your union – Toote May 17 '12 at 00:50
  • Thanks, you've both been a great help – puromonogatari May 17 '12 at 01:17
  • You're right, I thought "intersect". and Wrote "union", http://stackoverflow.com/q/2300322/340140 – user340140 May 17 '12 at 23:18