SQL Table Design - Identity Columns

Question

SQL Server 2008 Database Question.

I have 2 tables, for arguments sake called Customers and Users where a single Customer can have 1 to n Users. The Customers table generates a CustomerId which is a seeded identity with a +1 increment on it. What I'm after in the Users table is a compound key comprising the CustomerId and a sequence number such that in all cases, the first user has a sequence of 1 and subsequent users are added at x+1.

So the table looks like this...

• CustomerId (PK, FK)
• UserId (PK)
• Name

...and if for example, Customer 485 had three customers the data would look like...

CustomerId  |  UserId  |  Name  
----------
  485  |  1  |  John  
  485  |  2  |  Mark  
  485  |  3  |  Luke

I appreciate that I can manually add the 1,2,3,...,n entry for UserId however I would like to get this to happen automatically on row insert in SQL, so that in the example shown I could effectively insert rows with the CustomerId and the Name with SQL Server protecting the Identity etc. Is there a way to do this through the database design itself - when I set UserId as an identity it runs 1 to infinity across all customers which isn't what I am looking for - have I got a setting wrong somewhere, or is this not an option?

Hope that makes sense - thanks for your help

Answer 1

I can think of no automatic way to do this without implementing a custom Stored Procedure that inserted the rows and checked to increment the Id appropriately, althouh others with more knowledge may have a better idea.

However, this smells to me of naturalising a surrogate key - which is not always a good idea.

More info here:

http://www.agiledata.org/essays/keys.html

Answer 2

That's not really an option with a regular identity column, but you could set up an insert trigger to auto populate the user id though.

The naive way to do this would be to have the trigger select the max user id from the users table for the customer id on the inserted record, then add one to that. However, you'll run into concurrency problems there if more than one person is creating a user record at the same time.

A better solution would be to have a NextUserID column on the customers table. In your trigger you would:

1. Start a transaction.
2. Increment the NextUserID for the customer (locking the row).
3. Select the updated next user id.
4. use that for the new User record.
5. commit the transaction.

This should ensure that simultaneous additions of users don't result in the same user id being used more than once.

All that said, I would recommend that you just don't do it. It's more trouble than it's worth and just smells like a bad idea to begin with.

Answer 3

So you want a generated user_id field that increments within the confines of a customer_id.

I can't think of one database where that concept exists.

You could implement it with a trigger. But my question is: WHY?

Surrogate keys are supposed to not have any kind of meaning. Why would you try to make a key that, simultaneously, is the surrogate and implies order?

My suggestions:

1. Create a date_created field, defaulting to getDate(). That will allow you to know the order (time based) in which each user_id was created.

2. Create an ordinal field - which can be updated by a trigger, to support that order.

Hope that helps.