Page 1 of 1

problem in designing a table => sort question

Posted: Sun Jul 18, 2004 3:54 am
by 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:

Code: Select all

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:

Code: Select all

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

Posted: Sun Jul 18, 2004 4:28 am
by JAM
Personally, it sounds allright enough. But someone else might shed more light on the subject.

To push it further, you might aswell change the Photo_Order field type from INT to MEDIUMINT, SMALLINT or perhaps even TINYINT, but thats depending on the application itself and how many photos you will allow being stored in an album.

Code: Select all

Type / Bytes used / Val unsigned:
TINYINT / 1 / 255
SMALLINT / 2 / 65535
MEDIUMINT / 3 / 16777215
INT / 4 / 4294967295

Posted: Sun Jul 18, 2004 4:33 am
by kettle_drum
It might be easier if instead of photo order holding the place where the photo should go it holds the id of the photo that should go before it. As at the moment when you add a new photo to the album and decide that it should go at number 2, you have to insert the photo and then edit each photo that comes after 2 and update its position - album with 200 photos and you have to do alot of edits.

Whereas if you simply hold the number of the photo in front then all you have to do is insert the new photo and change the old entry to point to the new photo instead of the one it currently points to.

4 <- 12 <- 1 <- 6

Add photo 20 at number 2

4 <- 20 <- 12 <- 1 <- 6

You can then use a function to grab the data and put it in a linked list (array) and it would be easiy to work with.

Posted: Sun Jul 18, 2004 4:46 am
by davidklonski
I thought about this solution.
I am however not sure how to use ORDER BY to sort the photo according to the defined order in the linked list design.
With the previous solution it was very simple, ORDER BY Photo_Order

Can you show me how the sorting would be in the linked list solution?

Posted: Sun Jul 18, 2004 5:20 am
by JAM
kettle_drum wrote:As at the moment when you add a new photo to the album and decide that it should go at number 2, you have to insert the photo and then edit each photo that comes after 2 and update its position - album with 200 photos and you have to do alot of edits.
Well, doesn't have to be that bad imho.

Code: Select all

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

Posted: Sun Jul 18, 2004 5:22 am
by davidklonski
thanks for this info. This is very interesting