SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem in designing a table => sort question

    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:
    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:
    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

  2. #2
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a linked list solution

    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?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use Photo_Order, not Previous_Photo

    not only is the ORDER BY trivially easy, it also works when you have a subset of photos

    for example, if you say WHERE something, and you only get a few photos back, how do you use Previous_Photo if the photo that it "links" to isn't among the ones returned?

    tip: assign values of Photo_Order by 10s or 100s, so that it's easier to renumber them

    e.g. in your example above, where the order is 8, 9, 23, 5 and 3, suppose you wanted to move 5 from 4th position to 1st

    if you've assigned Photo_Order sequentially, you have to "renumber" most of the rows in the table, whereas if the initial values of Photo_Order were assigned in 100s,
    Code:
    Album_ID   Image_ID   Photo_Order 
    4              23            300
    4              8             100
    4              3             500
    4              9             200
    4              5             400
    then in order to move image 5 to first position, all you have to do is update its Photo_Order value from 400 to 90
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, can I ask you some questions in MSN messanger?
    I could really use your expert knowledge in designing a few complex tables.

    If so, please send me an email to: davidklonski@fastmail.fm and I will reply you with my MSN account details


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •