I’m creating a kind of calendar for users. There is one “global” calendar which includes events. The events are associated to a month but they don’t have any exact date, they do have an order in the month though.
What I have now is calendar_month which has many calendar_events. The events has a title, calendar_month_id and position. This works fine (it’s no problem to change this).
Now, each user should be able to move the events, both within a month but also move each event to another month. If the original event is changed, i.e. the title is changed, it should be reflected in the user’s moved events (which means I can’t duplicate the event if a specific user moves it). Of course, if one user moves an event it should only be moved for them, not for all users.
I’d have another set of calendar_events called something like calendar_events_user or something where its primary key is a combination of a foreign key to the primary key of calendar_events and foreign key to the primary key of the user (basically a many-to-many, with extra data), but otherwise copies it in full. This way you could have a user completely override a global event with their own settings but it would not affect the main one. When you grab them from the database just use COALESCE or something to grab the user one first if it exists, else fall back to the global one.
That is why you should get events based on the coalesced listing then order based on those.
That way for a given user they will only see their events and the globals of which they have no personal event for, then any ordering you do based on those results will be fine.
The ordering you show does not give any information on what you are ordering ‘by’? Are you ordering by some global ordering key, or by the event start time or something?
Sorry that I was unclear, I’m ordering based on the position field. That’s why my previous example gets a bit messed up.
Jan event 2 is a global event, it has a position of 2, the user has no personal event for this. So if the user moves two other event between Jan event 1 and Jan event 2 the position column makes no sense.
Jan event 1 (global, position1)
Feb event 1 (personal, position 2)
Feb event 2 (personal, position 3)
Jan event 2 (global, position 2)
If you have duplicate field entries then you’ve gotta refine on something else.
Also if you are ordering based on position then there is no way at all that it would order like you show, it would order either like:
Jan event 1 (global, position1)
Feb event 1 (personal, position 2)
Jan event 2 (global, position 2)
Feb event 2 (personal, position 3)
Or like:
Jan event 1 (global, position1)
Jan event 2 (global, position 2)
Feb event 1 (personal, position 2)
Feb event 2 (personal, position 3)
But absolutely not like how you show. But regardless, if you have duplicate positions then you’ve gotta choose the next field to order on, like time or something.
Oh yes, definitely. The way I showed the order is the way the user moves the items and how the user wants to have them display. It all falls down due to the position value of “Jan event 2” as you mentioned.
I’m not sure what other data I could order on though.
With “duplicate positions”, do you mean that if I have two events with position 2 I need a a tie break? Because in this case, I don’t think that’s the biggest issue, the biggest issue is that I need “Jan event 2” to be placed last in this example, even after “Feb event 2” which has a position value of 3.
Jan event 1 and 2 are both global events and thus have a position relative to each other, while the Feb events are personal events (after they’ve been moved by a user) and have positions relative to both global and personal events. Right?
What would solve the problem would be to create a personal event for all events after the events that I’ve moved. So in my example I would create a personal event for “Jan event 2” as well with a position value of 4. But that seems a bit annoying
Then add ‘another’ position, like subposition or so in the user’s event table, and break the tie on that?
You could also just change the position in the users table anyway, so if they wanted that last january one at the bottom then copy it into their event and make the adjustments as necessary on that copy?
Well it ‘is’ a personal ordering. But you an always break a tie with a subposition, or make the users event position a float instead of int and have the first be 1.1 and the next be 1.2 or something, that way it is below the 1 position and above the 2 position.
