Viable ways to have an AutoNumber per user (or per entity)?

Let's say you have a web application that manages books for book sellers, and it is built on a multi-tenant database with a single books table that contains books from several book sellers.

Now let's say that each book seller really wants each of their books to have a unique number associated with it so they can look books up by that number, but it's important to them that the number is roughly consecutive for them. (It's OK if there are small breaks in the sequence due to deleted books and other events that cause an AutoNumber to get consumed but not used).

Obviously each book already has a unique number (primary key) associated with it that is generated via AutoNumber and is unique across book sellers. That is not what I am discussing here.

Let's just assume SQL-Server from here on, but th开发者_如何转开发e discussion applies equally to Oracle (except that Oracle uses Sequences that are independent of tables, and the current version of SQL Server must use a table to accomplish the same thing).

We want a number that increments safely in the context of a book seller. We want to maintain the benefits of using AutoNumber, but we want there to be one sequence per book seller. It seems like there are two options, and neither are very good:

1. Create one single-column table per book seller. This scares me because I can't think of another example of dynamically changing the schema (adding a new table whenever a new book seller is added to the system via the web application) in a web request. It also seems really heavyweight to have one table per book seller. I know a future version of SQL-server will support Sequences, but even that would still be a schema change at run time.

2. Roll your own auto-numbering behavior. This seems really risky because databases' built-in AutoNumber features take care of a lot of stuff for you, and giving that up is a big deal. Attempts to re-implement it yourself are probably error-prone and may cause poorer concurrency than the built-in AutoNumber.

Hopefully there are additional options that I'm missing. Has anyone successfully dealt with a similar situation? Thanks.

Is there a reason you couldn't have a 2 field table with:

BookSeller_ID, BookID

You wouldn't need to change schema as you add sellers, and it would be trivial to track per seller:

SELECT MAX(BookID)
WHERE BookSeller_ID = 123

For additional info you could also add a Universal_BookID field that linked to your unique ID referenced in the 3rd paragraph.

EDIT:

To clarify, if you have sellers 1 2 and 3 you could have a table like:

SellerID    BookID   BookUniversalID
1           1        123
2           1        456
3           1        999
1           2        1234
1           3        8798
1           4        999
1           5        10000001
3           2        123
3           3        456

You keep track of which seller has which IDs assigned and which actual book it links too, and to determine what the next ID is for a seller just query

SELECT MAX(bookid) FROM ThisTable WHERE SellerID = 1

DENSE_RANK, works in SQL Server and Oracle

Assuming your table looks vaguely thus

CREATE TABLE dbo.BOOKS
(
    internal_book_id int identity(1,1) primary key
,   seller_id int NOT NULL
,   title varchar(50) NOT NULL
)

Whenever you present the identity value to the seller, use the dense_rank() function to generate the surrogate values.

CREATE VIEW dbo.BOOK_TO_SELLER_MAP
AS
SELECT
    B.*
,   DENSE_RANK() OVER (PARTITION BY B.seller_id ORDER BY B.internal_book_id ASC) AS unique_book_id_for_seller
FROM
    dbo.BOOKS B
WHERE
    B.seller_id = @sellerId

For the combination of seller_id and the generated id, you ought to always match back to the true id (assuming no physical deletes).

Demo code

;
WITH BOOKS (internal_book_id, seller_id, title)
AS
(
    SELECT 1, 100, 'Secret of NIMH'
    UNION ALL SELECT 2, 400, 'Once and Future King'
    UNION ALL SELECT 7, 88, 'Microsoft SQL Server 2008'
    UNION ALL SELECT 8, 100, 'Bonfire of the Vanities'
    UNION ALL SELECT 9, 100, 'Canary Row'
    UNION ALL SELECT 10, 400, '1916'
    UNION ALL SELECT 11, 100, 'The Picture of Dorian Gray'
    UNION ALL SELECT 12, 88, 'The Disasters of War'
)
, BOOK_TO_SELLER_MAP AS
(
    SELECT
        B.*
    ,   DENSE_RANK() OVER (PARTITION BY B.seller_id ORDER BY B.internal_book_id ASC) AS unique_book_id_for_seller
    FROM
        BOOKS B
)
SELECT
    *
FROM 
    BOOK_TO_SELLER_MAP V
ORDER BY
    V.seller_id
,   V.unique_book_id_for_seller

Results

internal_book_id seller_id title                      unique_book_id_for_seller
7                88        Microsoft SQL Server 2008  1
12               88        The Disasters of War       2
-------------------------------------------------------------------------------
1                100       Secret of NIMH             1
8                100       Bonfire of the Vanities    2
9                100       Canary Row                 3
11               100       The Picture of Dorian Gray 4
-------------------------------------------------------------------------------
2                400       Once and Future King       1
10               400       1916                       2

OMG Ponies is correct that sequences are the only correct way to achieve this. There isn't really another viable option.