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:
PicId (BIGINT, AUTO INCREMENT, PK)
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:
- 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.
- 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.
