0

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:

alt text

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 number, 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!

Improve this question
7
  • 1
    Why do you care that they are sequential? This is quite a hassle to implement and after a few deletes you would get gaps in the sequence anyway. Commented Dec 3, 2010 at 12:15
  • I can't vote because I'm an unregistered entity here, but Martin's comment is spot-on. There is really no valid reason to want ids to be sequential. All you need to do with id is to distinguish one entity from another; a guid would serve that purpose equally well (though it would be a little unwieldy and take up more space on outputs, of course). Be content with the uniqueness and forget about the sequentiality of id. You can always get a count of customers for a company : select count(id) from customer where companyyid=? The problem you want to solve is self-created. Commented Dec 3, 2010 at 12:35
  • Ok, I get your point. This might be okay on customers, but when it comes to orders and invoices it is essential that the invoices have a continously unbroken chain of invoice numbers. If there is a gap in the number series on invoices I do get a problem with my auditors. But I guess your answer to this is to control it in my system rather than in my database? Commented Dec 3, 2010 at 13:00
  • @Martin - you do know that if a transaction gets rolled back due to any kind of error, the IDENTITY value doesn't roll back, don't you? So if anything like that can happen, IDENTITY isn't a suitable solution for your order/invoice numbers. Commented Dec 3, 2010 at 13:16
  • Yep. If you need an unbroken chain you will need to implement your own solution that serializes inserts in case any get rolled back. Commented Dec 3, 2010 at 13:28

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.