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).

  • Don't attempt to create a generic system for this, simply code the priority order per profile. ie. run different sql depending on the profile type
    – Ewan
    Mar 8 '21 at 19:17
  • Is it possible to pre-calculate the values? Then you could just store them in another table and look up the different priorities when you need them. Mar 8 '21 at 21:53
  • @FrustratedWithFormsDesigner they will be set in some constants (updated post). The thing is (I might have not been clear about this in the question initially), the order might be going through different actions at the same time, which blocks me from (for example) just changing the priority from 5 to 4 when an action happens.
    – brunouno
    Mar 9 '21 at 17:09

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.