I don't think that slapping a "priority" on to the end of a table can pass for a solution. In order for the approach to meet the efficiency requirements, the width of the priority field would have to be 1 + the maximum unsigned value of the PK field, or else the field could run out of granularity. My PKs are generally 64 bits, so that means my priority field would have to be 4 billion times wider than the addressable space of a 32-bit operating system. Since I don't have the resources to babysit that much data, that option is definitely out.
One could save space by redistributing the values of the priority field on demand, but that won't ever be able to scale - you'd have to lock the table too often and for too long. I think that the most admirable part of Cayenne is that, no matter how many cool things it can do, there's always an intuitive understanding of what it's doing under the hood, even if the algorithms involved aren't directly observed. This has a lot to do with the fact that Cayenne sticks to the basics; there's a clear mapping between what you do in the ORM world, and what happens in the RDB world. Deviating from that philosophy would really turn me off, and a perfect example of doing so would be to redefine what it means to add a value to a relationship list. Adding to the relationship collection is the equivalent of doing an INSERT, and it goes no deeper than that. As long as that metric is maintained -- and I do hope that is forever -- the only way forward is to decorate what it means to be a relationship to account for this special case. Ultimately, the problem has to be solved in the schema. Whatever approach you choose to take, and whether or not Cayenne respects that behind the scenes is irrelevant if the schema isn't modeled to cater to the needs of the application. We need to find a design pattern that everyone can agree on. I'd like to ask anyone who is reading this, and has the time, to say what would be important for them if Cayenne were to approach ordered relationships. For me, scalability is always a high priority. I see the value in being able to jump to the middle of the list quickly, so a linked list won't work well when the chain gets long. Simplicity is also important. The databases I interact with via Cayenne are not exclusively touched by Cayenne, so if the approach requires me to fill up my database with all kinds of non-intuitive relational tables, I'm not going to take that approach. Since it seems that simplicity (no extra tables), scalability (no linked lists), and speed (no sorting) can't all co-exist simultaneously, I think there has to be multiple solutions. The developer will have to pick which one works for them, but hopefully they will be able to all spawn from the generic List interface.
