• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Breaking Relational Databasing - what's the best practice???

#1
[eluser]boltsabre[/eluser]
Hi, I've got myself into a situation with my databasing that I've never had to tackle before, just looking for some general insight to the issue.

I've an image gallery (with various 'categories'), and images can belong to a maximum of 3 categories (and must belong to at least 1 category).

When a user goes to a certain category, I need to build a sql select statement like this:
Code:
SELECT id FROM img_gal_images WHERE (and this is the problem...)
To get all the images for that category (using pagination to display 30 images per page)


Thoughts on how to do this include:
1. In the 'img_gal_images' table have a column called 'cat_ids' (varchar 12) which stores a string containing the category ids separated by (whatever, pipe, space, comma).
Then my sql "WHERE" statement could do a text %wildcard search on that column searching for the category id.
However, due to pagination, I need to do a 'count' on the entire table of images what belong to a certain category (Initial gallery will have 200-300 images, but could grow to 10,000+ over the next 2-4 years).

2. Have a new table "img_id_by_cat_id" which has "cat_id" and "img_id" columns, and each image gets a row in this table for each category it is in (min. of 1, max of 3).
With indexes on both columns, I suspect this will be much faster than idea 1 using wildcards for getting the image count for pagination. The sql used to find images in this table that belong to a certain category would be something like:
Code:
SELECT COUNT(img_id) AS c FROM img_id_by_cat_id WHERE img_id = $img_id AND cat_id = $cat_id
But when actually getting the 30 pictures for that page I would then have to have my select statement reference both tables, something like this:
Code:
SELECT id, height, width, alt_text, etc
FROM img_gal_images, img_id_by_cat_id
WHERE img_id = $img_id

If anyone has any experience in similar situations, I'd be very grateful for you input on what you think is the best method to implement. Or is there some better method I have not even considered, or any pitfalls to either of the above methods that I haven't considered.

Many thanks in advance guys and girls!

#2
[eluser]jmadsen[/eluser]
The standard practice is to do the second one.

You have an images table, a categories table, and a join table called "images_categories" or similar.

best to work through a few tutorials on sql & schema design if this is all new to you.

#3
[eluser]boltsabre[/eluser]
There is no "categories" table as such (categories have been isolated to a helper function, which returns a multi-dimensional array to minimises DB hits and inherent caching problems. Categories are set in stone - if I need to add more in the future I'm happy to add to the array manually in the file itself - it's a personal website, I'm not worried about the client ringing up every 3 days saying "add this cat, remove this cat", or wanting the functionality to add/edit/delete/deactivate/archive categories, etc).

Quote:best to work through a few tutorials on sql & schema design if this is all new to you
It's not so much as new (I learnt it when I was studying, I'm well aware of the concept of join table to create a relationship between two entities, and how to implement it correctly), but considering I don't have two tables to join, I was wondering if there was a better way (I terms of speed of db requests).

Thanks for the input, unless I hear back regarding some other idea, I'll go with the second, seems the easiest to implement and create my admin "image upload" script.

Thanks for the input jmadsen!!!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.