Data structures - prioritizing records based on pending actions

Question

I need to add a priority column to an orders database to sort them, which will vary depending on what has already been done (actions taken). The other catch is: there are two different roles that will have both shared and exclusive actions so the priority changes depending on who is viewing the order list.

Profile 1
Approve order   - Highest priority
Review options  - Second highest
Processing      - Third highest (shared)
Other actions   - Low priority, no sorting impact

Profile 2
Confirm order   - Highest priority
Processing      - Second highest (shared)
Other actions   - Low priority, no sorting impact

I thought about assigning numeric values to them and adding a total but that will not work since I have separate actions. I've also thought about a numeric string (something along the lines of 554421) which will be changed each time an action is taken. The problem is, it might get messy if in the future more actions are available with different priorities. What would be a better approach?

UPDATE: The actions' priority scores, along with their name, will be fixed in a constants file. Another condition is, some orders might be going through more than one action at the same time which prevents me from just changing priorities from one digit to the next.

Approve order   5
Review options  4
Confirm order   3
Processing      2

So the same order might be reviewed (4) while pending confirmation(3).

Answer 1

The simple solution here is to create a separate table that 'hangs off' of the order table. Something along the lines of:

Order | State | User
--------------------
  100 | 4     | 128
  100 | 3     | 583
  101 | 2     | 128
  101 | 3     | 583

I've substituted the word 'state' for 'priority' here because it seems to match what you are doing better. You can see here that the same orders are assigned different states for different users. If the various states that a single order can be in are not based on user, then modify the table to reference that instead.

Then you can join theses states to the orders based on the user and sort on states as desired. If you need to store the sorting rules by user profile, you can also store that in another table:

Profile | Status | Priority
---------------------------
      1 | 5      | 1
      1 | 4      | 2
      1 | 2      | 3
      2 | 3      | 1
      2 | 2      | 2

And each user is assigned a profile. You can then join this with the table above and the orders table and add an ORDER BY on priority to the query.