0

I am pretty new to database schema design. So this may sounds trivial to experts

I am designing a app that store photo for each trip I created. So, I have one table storing trips and one table storing some photos belonging to each trips.

So, things seems easy for designing this table . And below are my drafted schema

Trips

ID 
title 
created_at

Photos

ID 
title 
created_at 
image_location
trip_id

Here is the problem: If I would like to show the order of photos, I can make use of the attribute of created_at in Photos. But what if one day I would like to rearrange the order of images or I inadvertently assign the photos with wrong order at the first time I add it into the trip.

What I come up with is that I try to make an column called order_no in Photos, so when there is new photos added, its order_no would be increased by one and assigned to it.

Photos

ID 
title 
created_at 
trip_id
order_no

However, the problem is that I will have to update every order_no to keep the sequence order consistent when there is a new rearrangement

For example , I would like to rearrange the position of photo4 , the original order is like below

1 photo1
2 photo2
3 photo3
4 [photo4]

1 [photo4]
2 photo1 (order_no need to be changed from 1 to 2)
3 photo2 (order_no need to be changed from 2 to 3)
4 photo3 (order_no need to be changed from 3 to 4)

Is there a consistent way to implement this ?

Thanks

  • Edit your example to include the sequence numbers – Strawberry Apr 12 '21 at 07:15
  • Updating all other rows from same trip is a natural consequence and not a big deal, unless the re-arrangement process happens continuously. You could also probably benefit from an index on `trip_id, order_no`. – Álvaro González Apr 12 '21 at 08:13
  • @ÁlvaroGonzález Thanks for reply, So are you saying updating all other photo's order_no from same trip would not be a expensive computation? Actually, my idea is that, every time I open the trip to see the photos included, I will make an join with Photo tables and then sort it according to the order_no. I also have concern if this operation is too slow within app. I have also thought about indexing but not so sure what I should index to make this access faster. When you mentioned index, are you talking about this problem? Sorry I didnt quit get your approach. –  Apr 12 '21 at 08:51
  • See for instance https://stackoverflow.com/questions/62660126/change-position-of-serial-number-in-sql/62660956#62660956 - but note that the example I've provided there uses @variables which are essentially now obsolete. – Strawberry Apr 12 '21 at 09:44
  • Just to make it clear... I'm assuming that order is per-trip and not global, i.e., count starts on 1 for each `trip_id`. The index I proposed addresses that use case. It shouldn't be a performance bottleneck unless you have millions of pictures in a single trip and/or you reorder them every second. – Álvaro González Apr 12 '21 at 09:49
  • @ÁlvaroGonzález , Exactly, not updated every second, the order is pre-trip. So order_no attribute is not unique. Just to make it clear The index you are talking about is like trip_id_order_no table `possible value` `trip_id:1` `order_no_seq: a bunch photos id` just curious, how faster it is compared to stored the sequence of photos_id order per trip than finding out all photos_id in photos table according to the trips_id ,by then sort it by its order_no to return.My concerns is that index approach still need go back to photos table to source the image_location one by one –  Apr 13 '21 at 08:45
  • @Strawberry , thanks, its quite close to what I need, There are two possible cases in which elements could be up or dragged down. My idea is to implement two logic to handle both cases. Your solution seems more handy and less repeative. However I dont quite understand the magic here ' GREATEST(@source,@target) THEN LEAST(@source,@target) ' could you explain a bit why we could generalise in this way? –  Apr 16 '21 at 08:35
  • We're dragging a value from A to B (which means assigning a new sequence number to all values between A and B. In order to know what that new sequence number should be, we need to know whether B is further up, or further down, the list than A. (If my logic is correct) The LEAST/GREATEST stuff is just a fancy way of saying that. There are probably other methods you could use. – Strawberry Apr 16 '21 at 08:42
  • @Strawberry oh no, I identity a bug there.... When we drag down, like from position 2 to position 5. The element in 2 was swapped with position 5 and element in position 5 swapped with 2. But it is not quite what we expected, since the sequence number of position 5 should be increase by 1 only..... The logic seems mixed with the concept of dragging and swapping –  Apr 16 '21 at 09:45
  • Well, when you fix it, let us know ;-) – Strawberry Apr 16 '21 at 09:50
  • @Strawberry I was puzzled and thought may be I was wrong coz you code seems very sophiscated at first haha –  Apr 16 '21 at 09:52

0 Answers0