Compound key problem - creative solution needed

[manitoba]

I use the same instance of the database to service multiple customers. The customer ID is an int generated through @@IDENTITY. This table looks like:

CUSTOMER
customer_id int,
...

I want all other tables to have customer_id as part of a compound key. For example, the table to store people would look like this:

PERSON
customer_id int,
person_id int,
...

Now here comes the hard bit. For each customer_id, I want the person_id to start with 1. For example:

customer_id: 1, person_id: 1
customer_id: 1, person_id: 2
customer_id: 1, person_id: 3
customer_id: 2, person_id: 1
customer_id: 2, person_id: 2

I've considered a solution like adding a column to CUSTOMER table such as "next_person_id". Then the application would get this value and increment it by 1. But there is a concurrency problem. When 10 threads try to read and update next_person_id field at the same time, the value in this field is incorrect. Instead of 11, you get 6 or 7.

Anyone have an idea of how I can create these compound keys?

 

[AndyInNC]

With the approach you're looking for, of course you can't use identity for the person_id. You could do all the updates to the tables through a stored procedure or handle it in the client code, but it's always best to take care of it at the lowest level. A trigger is about the lowest level you can get. Every single time, regardless of where the data is being added from, (client, stored proc, typing directly into a table, etc) an insert trigger would be first in line to make sure the data is handled correctly.

When inserting a new record, I assume you've got a mechanism to add the correct customer_id into the table, so don't add the person_id at this point. (Allow NULLs in the person_id.) Then run something that grabs the Max person_id for the newly inserted customer_id; if it's NULL, the new value should be 1, otherwise do Max(person_id) + 1. That should get you in the neighborhood you want to be in.

Also, as a suggestion, don't set this as a primary key (even though it should be unique.) Create a RowID either as Identity or uniqueidentifier and set it as the primary key. That will allow a little more flexibility in manipulating your compound key in the future if the need arises. (Something always arises.)