Custom PrimaryKey Generation with autoincrement

My question is the same as this one: Custom PrimaryKey Generation with autoincrement. But with a little twist.

When i built this database I had only one company and one set of customers, but now I run two companies, with different sets of customers. My first thought was to make two separate databases. But if I made changes to one of them I had to do the same to the other one, and I did not want to do this. So I decided to run both companies through the same database.

I have one table named Customer and it looks like this:

ID is the PK and also set to auto increment. This was working all good, until I decided two run two companies in the same DB. After I decided this, I added a column named CompanyID to attach that customer to a spcific company. This is also working like it should, when I log into my system as a user of company 1 I get compny 1's customers and so on.

But the problems appear when I add a new customer to the database. I want the customer ID's to start at 1 in both companies. But as it is now, when I create a customer in copmany 1 it gets customer number 1, and when I create a new customer in company 2 it increments on the same n开发者_高级运维umber, so it gets customer number 2. Here I wanted it to be customer number 1. The two increments should be separated based on the CompanyID.

First step might be to add both ID and CompanyID as PK, but what else should I do to accomplish this?

Also, if you got another opinion on how i should solve this, I would like to know!

Okay, first, you definitely need to define your PK against both CompanyID and ID, if you're going to accomplish this task (otherwise, you'll get a PK conflict when you try to insert Company 2's customer 1, assuming Company 1 already has a customer 1.

Once you have a requirement of "no gaps", you pretty well have to roll something yourself, rather than using the IDENTITY features in SQL Server, and you're going to lose scalability.

Two obvious options are to select MAX(ID) + 1 from this table for the next ID (but in this case, you have to maintain an exclusive table lock between determining this value and performing the insert), Or to maintain a separate table of Next IDs to be consumed (in which case you will need an exclusive row lock against the row that you're using and incrementing). E.g. you might have a table like so:

CREATE TABLE CompanyCustomerIDs (
    CompanyID int not null,
    NextCustomerID int not null,
    constraint PK_CompanyCustomerIDs PRIMARY KEY (CompanyID)
)

Then your insert into the Customers table would look something like:

declare @CustomerID int

begin transaction

update CompanyCustomerIDs WITH (ROWLOCK,HOLDLOCK,XLOCK) set @CustomerID = NextCustomerID = NextCustomerID + 1 where CompanyID = @CompanyID

insert into Customers (CompanyID,CustomerID,/* Other COlumns */)
select @CompanyID,@CustomerID,/* Other columns */

commit

As I said though, this will have an impact on the scalability of the database.

The best way to do this would be to have a Company table that took care of assigning its own primary keys, and set up a foreign key relationship between Company.CompanyID and Customer.CompanyID. Then store the company's information in the Company table, and the company's customer's data in the Customer table. This is the primary use of a RDBMS like SQL Server, which is to store the data in a relational way in order to efficiently manage the data.