I am designing a social networking website (mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:

Path (VARCHAR) : Path To Image
AlbumId (Foreign Key to Table containing album information, of which the photo belongs to).
Sequence (Image index or order in the album)
Status (CHAR) eg. u:uploading, a: available ,etc.
.. Other information

Now, suppose a user is uploading multiple photos at once and the order in which they arrive at the server is known only by the index value which I send to the server. So, what should be the best practice to store this order, subject to the following constraints:

  1. Suppose there are already some images, so I need to number the current sequence after the existing ones (i.e. max(Existing Sequence)+ <current squence> where albumid=<the desired album>). So, one of the solution is to use an int column.
  2. But, whenever the user would reorder the image, or insert into the middle, the whole set of rows from insertion point till the end would need to be updated. So, I am thinking of using float as an alternative. So, Sequence number of new image=(Sequence no of Old+Sequence no. of next)/2 . But this would lead to floating point comparisons as well as calculations.
    Another approach may be link-list approach i.e. Instead of Sequence no., I can use Next Image, but in that case, I would not be able to get the PicId of the next Image if it has not been uploaded yet!!!

Please help me regarding this.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.