problem in designing a table => sort question

davidklonski

Hello

I am designing a table which will define the relationship between photos and albums. Each album has an id and also each photo has an id.
The following table defines this relationship:

CREATE TABLE photo_in_album (
  Album_ID INT UNSIGNED NOT NULL,
  Image_ID INT UNSIGNED NOT NULL,
  Photo_Order INT UNSIGNED NOT NULL,
  PRIMARY KEY (Album_ID, Image_ID)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

The user should be able to define the order of the photos inside an album. This is where Photo_Order column comes into play. This column defines the order of the photo inside the particular album.

Here is an example:
I have 5 photos (ids: 23, 8, 3, 9 and 5) in album 4.
The order of the photos in that album should be: 8, 9, 23, 5 and 3.
The table will then look like:

Album_ID   Image_ID   Photo_Order 
4              23            3
4              8             1
4              3             5 
4              9             2
4              5             4

I will then use the Photo_Order column to sort the photos
Does that sound like a good solution?
Will I have efficiency problems when the user changes the order of photos?

regards
David

bubblenut

Yes, that is how I would do it. There are a couple of small changes I would make though. First, I'd add a single column unique_id, it makes indexing easier and I thing it looks a little cleaner but it's a judgement call. Two other points (well, one with two implementations) that are a little more important are a couple of uniques to restrict the data in the table. I would add a unique on Album_id and Photo_order to prevent having more than one order element per album. In the example you gave there is nothing to stop you changing image_id 23's photo_order to 1 thus giving you two photo_order 1s for that album, see? I would also add a unique on album_id and image_id to prevent the same image apearing more than once in the same album. To find out about how to use unique with mysql read here

HTH
Bubble

davidklonski

what about this solution?

Give the photo order a linked-list behaviour.
Instead of the Photo_Order column, I will have a Previous_Photo column

The Previous_Column for photo A will hold the ID of the photo that comes before A in the correct order.
I will use the reserved ID 0 to indiciate the first photo in the order.

This solution would save a lot of updates. Imagine for example, adding a new photo in the middle of the current order. I will have to shift all the photos who come after the new photo in the order.

The problem is that I don't know how to sort the photo based on this new mechanism.
In the previous solution I coud have used: ORDER BY Photo_Order
Now what can I do?

bubblenut

Yes, the problem would be the sorting. You will have to make a decision about what is going to be done more, updating or reading (I would have said probably reading but that's just a guess) Sorting lists in php is much slower than in a database but then on the other hand lots of updates would also be tiresome for the database (poor little DB 🙁) I would suggest a little benchmarking. I'm quite interested by this now so I'll give you a hand if you want. I'd suggest an ICQ chat to figure out the fine details of the testing and then posting up the results here when we're done if you're up for it?

davidklonski

I don't use ICQ, but mail me at
davidklonski@fastmail.fm and we will work out a solution.

davidklonski

what about the following:

Assume the I would like to add a photo that will be 2nd in the current sorting:

update table set photo_order=photo_order+1 where photo_order >= 2;
insert into table values(x, x, 2);

wouldn't that take care of of the shift automatically?

bubblenut

MSN? We really need something quick to chat through otherwise we may as well just chat here. I'll assume you don't use MSN either and start talking. If you could post up your tabl structure for the three tables I'll build the database localt on my machine and then start working on benchmarking.

Here's a little timer class I use

<?php
class Timer
{
  var $timers = array();

  function Timer() {
    //nothing
  }

  function timerStart($name = 'default') {
    $this->timsers[$name] = $this->getTime();
  }

  function timerStop($name = 'default') {
    return bcsub($this->getTime(), $this->timers[$name], 6);
  }

  function getTime() {
    $time_portions = explode(' ', microtime());
    return $time_portions[1].substr($time_portions[0], 1);
  }
}

davidklonski

I have opened an MSN account for this.
I will give it to you mail, so please write me an email with your email address and I will respond with my msn acocunt settings

davidklonski

I have come to the conclusion that the best solution would be to use an absolute positioning for soring in the database.
The reason that the linked list solution will not work is becuse whenever I use a WHERE clause there is a good chance that image A will be returned, but A's previous will not match the WHERE condition, and so I will not know how to sort the returned subset...

thanks for all the help