+----+---------+---------+-----------------+
| id | view_id | user_id | ordinal_ranking |
+----+---------+---------+-----------------+
| 1 | 10 | 1 | 1 |
| 2 | 10 | 2 | 1 |
| 3 | 11 | 1 | 2 |
| 4 | 11 | 2 | 2 |
+----+---------+---------+-----------------+
So suppose I want to insert new rows for view_id 12 and for both user_id 1 and 2, these 2 new rows should have ordinal ranking of 3.
How do I do that with minimal number of queries?
Bear in mind, I have many users who may all have different number of views assigned.
Another corollary to the problem is when I need to delete a particular view, say view 10, all the users affected should have the ordinal ranking re-ordered.
How do I do that also with minimal number of queries?
EDIT
A comment was made about the number of views per user. I would like to add that you may safely assume that there is a users
table and one column stores the number of views for each user record.
I am also okay if the ordinal ranking is zero-based, ie the ranking is 0, 1, 2, 3, etc
I also prefer not to use stored procedures.
EDIT2:
+----+---------+---------+
| id | name | view_count |
+----+---------+---------+
| 1 | Sal | 2 |
| 2 | Sam | 2 |
+----+---------+---------+
Is the users
table
the ordinal_ranking
merely reflects the personal choice of a user with regards to the views she can view.
E.g. user 1 Sal likes to have view 10 to be ranked higher than view 11.
ordinal_ranking
but you have not told us what table/column has the number of views.user
table.