How to render in a user defined sort order?

Question

We have a requirement to retreive books in a user defined order (e.g. Assuming there are 3 books - BookA, BookB, BookC), its possible that an end user would like to see this rendered as (BookB, BookA, BookC) or (BookC, BookA, BookB).

1. We are thinking of adding a integer column (e.g. sortColumn) to capture this order. Is this a good option, if the list of items is going to be huge?

2. If we go with the above approach, what is the best way to re-calculate the sort order post ordering as it might involve updating multiple records every time a book is either moved up or down.

3. Assuming there are 1000 books, how do you decide on the 'sortColumn' value for a new book which is being added.

Answer 1

if you're using Hibernate or JPA it's as simple as having a List on your domain object and store the ordering of the list in a column (Hibernate/JPA will automanage it for you) Then you can interact with List to do reordering.

User->has list of UserBooks, a UserBook has an User, a Book and an Ordering

if you use hbm.xml's :

    <list name="books" lazy="true" cascade="all-delete-orphan">
      <key column="iUserBookId"/>
      <index column="iOrdering"/>
      <one-to-many class="foo.bar.UserBook"/>
    </list>