Which way is better to implement a waiting list in my database?

Let's say I have a USER table, and each my database is to handle some kind of a waiting list. The waiting list is something like a priority queue (it's not FIFO, there is a value that will determine the queue position). I am thinking of having another table WAITINGLIST, containing two columns, representing the user IDs (foreign key) and a the user's priority in the waiting list. Is this a good design? Everytime there is a vacancy, the highest priority user will be served and removed from WAITINGLIST.

What is the relationship called? Is it one-to-one? Bu开发者_如何学运维t not all user exist in the WAITINGLIST.

Is it a good design? I read that one-to-one is rarely good design. I can also just put this priority value as another column in USER, but since USER is much larger than WAITINGLIST, I guess performance will be much better by using WAITINGLIST (much fewer rows to sort).

EDIT: the priority aren't some limited, discrete value like (0 - 5, low - high), but it's a floating point value (result of some calculation), and it will be very rare for the same user to have the same priority, so the problem here is not about choosing when two user have the same priority.

---

Having a WAITING_LIST table is not necessarily a bad design. If the only thing that you need to know about a waiting list position is the score (and the User ID) then it is debatable. If there is any possibility that you might want something else to do with the waiting list, like the date and time the record was added, whether there is some interim status information like a code saying "pending evaluation" or something like that, then a 1:1 relationship is a perfectly acceptable database design.

You really have to be careful with rules of thumb. The rule of thumb that says 1:1 relationships are a bad design is based on the fact that a lot of people don't know when and how to use them properly. When you are dealing with a sub-type then 1:1 is perfectly OK.

In your case, a WAITING_LIST table is a work list. The work list could well be of interest in and of itself so modeling it as its own table is fine. If you only have one attribute in this table (the priority score) then the need for the table is weak, but it's not bad to have the table anyway, especially if there is any substantial risk that other attributes might be added later, or if you might change to a mode where the work list is not deleted, but tracked for historical purposes, in which case the relationship could become 1:many.

---

And what about a third column in the WAITINGLISt table which stores the timestamp when you insert the row ?

You could then orderby priority and timestamp, in case of 2 rows have the same priority.

---

If working in Oracle, an index on USER.QUEUE_POSITION would be very small, as users not queuing for service would have a NULL value for USER.QUEUE_POSITION, and therefore would not be in the index.