problem in designing a table => sort question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

problem in designing a table => sort question

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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)
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

thanks for this info. This is very interesting
Post Reply