How to save the database information about the order?

I have a table that holds the card, which is described by the following attributes

Database: Oracle 11g

They USER_TABLE to many (- <) CARD

CARD

id NUMBER PK
name VARCHAR
card_number NUMBER
USER_ID NUMBER USER_TABLE FK

Probably the tables will store about 20 million records

In my application user (from the table USER_TABLE) is able to determine the order assigned to each card.

Cards by downloading through the query: SELECT * FROM CARD WHERE USER_ID =?

My idea to solve the problem of order:

1st ORDER NUMBER adding an attribute that will determine the order. Unfortunately, after changing the order I have to update all user records.

2nd The addition of two attributes PREF NUMBER NEXT NUMBER. I Gain a little on operations where the card position changes by one, but in the worst case I update all the user cards.

My questions are as follow开发者_运维技巧s:

How to eliminate the problem of blockages in table?

Is there any better way to solve this problem?

Make your ordering id a FLOAT. Then you can modify the value to be between two pre-existing values.

myID  myDataID  myOrder   =>   myID  myDataID  myOrder
 1       1        1.0     =>    1       1        1.0
 1       2        2.0     =>    1       2        2.0
 1       3        3.0     =>    1       3        1.5

MyDataID 3 has now moved from being 3rd to being 2nd. And I have not had to update the myOrder values for any other records.

Most sorting follows a scheme based on a real table attribute - primary key, created date, salary. Occasionally there is a need to disrupt a sorting scheme, for business reasons. A classic example is COUNTRY drop-downs on web sites:all the countries are listed in alphabetical order, except the United States which is first. This reflects the size and importance of the American customer base (at least as far as English-speaking web sites go).

There are only 196 countries in the world (well, probably), so it wouldn't be too great a hardship to impose an arbitary sorting scheme for all of them, not just the USA. But is your user really going to assigned a preferred sort order to each of ~20 million records? It seems unlikely. Probably what they want is an autonomic sort order with the ability to override for certain preferred records (like the country code for USA).

If that is the case, what you need is one column which is optional. The user assigns a preferential order to the records they care about, and the others are left to default to (say) unique key order. Like this:

select * from big_table
order by nvl2(overriding_sort_order, 9999), uniquw_key
/

Of course, if the user wants to change the overriding_sort_order for one record, they will have to handle the ripple up/down - or you will have to handle it for them. The point is, only a relative handful of records is affected, rather than the entire table.

"What about order of preference? 3 credit cards, ordered by which I prefer to use. Or a list of DVDs, showing which I'd prefer to receive next."

An order of preference for an individual user is a different business case. "three credit cards" is a different example from the "20 million" records cited in the original question.

So, let's talk about your DVD example. You are not ordering the entire LoveFilm inventory, you are creating a table which identifies and sorts a very small sub-set of available DVDs for each user: something like:

USER_WISH_LIST
--------------
User_id
DVD_id
Preferred order

The number of DVDs each user will specify is likely to be a handful or so, because the sort order is only applied to each User's chosen DVDs. So the overhead in re-jigging the PREFERRED_ORDER column if the user decides they want to watch Mona Lisa Smile before Straw Dogs and after Helvetica, instead of after Johnny Mnemonic and before Man on Wire, is perfectly manageable .