Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
XP is just a number
 
PerlMonks  

Re^2: Building an index for next/last in a photo album.

by Seumas (Curate)
on Jul 26, 2004 at 20:46 UTC ( #377578=note: print w/ replies, xml ) Need Help??


in reply to Re: Building an index for next/last in a photo album.
in thread Building an index for next/last in a photo album.

The problem is that this application will serve infinite users with infinite albums, so while img_id is unique and sequential, it doesn't gaurantee that any two images belong to the same person or album.


Comment on Re^2: Building an index for next/last in a photo album.
Re^3: Building an index for next/last in a photo album.
by davidj (Priest) on Jul 26, 2004 at 21:29 UTC
    Ok, this may or may not be a good solution, depending you whether or not you want to mess with your database design, but it will work.

    Somewhere in the table put another column for a sequence number. So, instead of
    img_id | album_id | uid | name | ext -------+----------+-----+--------------+------ 38 | 14 | 89 | mypetrat | jpg 39 | 14 | 89 | mypetlemur | jpg 40 | 2 | 12 | vacation_01 | jpg 41 | 14 | 89 | mypetvulture | jpg 42 | 2 | 12 | vacation_02 | jpg 43 | 2 | 12 | vacation_03 | jpg 44 | 14 | 89 | mypetcow | jpg
    you have something like
    img_id | album_id | seq_id | uid | name | ext -------+----------+--------+-----+--------------+------ 38 | 14 | 1 | 89 | mypetrat | jpg 39 | 14 | 2 | 89 | mypetlemur | jpg 40 | 2 | 1 | 12 | vacation_01 | jpg 41 | 14 | 3 | 89 | mypetvulture | jpg 42 | 2 | 2 | 12 | vacation_02 | jpg 43 | 2 | 3 | 12 | vacation_03 | jpg 44 | 14 | 4 | 89 | mypetcow | jpg
    You could then grab the seq_id to the corresponding img_id
    select seq_id from TABLE where img_id = $img_id
    Then 1) calculate prev and next seq_id and 2) grab the img_ids you want be referencing the seq_id
    my $p_seq_id = $seq_id - 1; my $n_seq_id = $seq_id + 1; "select img_id from TABLE where seq_id in ($p_seq_id, $seq_id, $n_seq_ +id)"
    You could easily keep the current album seq_id value in another table and reference it when adding new images. As I said, it may be a hassle modifying your database, but it may be worthwhile in the long run as it more easily does what you want than the other options.

    Note: This may be a good "teachable moment" as educators like to call them.

    Perhaps the most important thing in designing an application that uses a database is to know thoroughly what you want from the application before you even begin designing the database. Too often, people design a database based on a less than thorough examination of the application requirements and end up having to redo it (the database.) :)

    davidj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://377578]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (10)
As of 2014-04-16 20:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (434 votes), past polls