Calculating A Primary Key VS Idenitity

Question

Calculating A Primary Key VS Idenitity

Adam Bagarella

SSC Enthusiast

Points: 154
More actions
September 7, 2006 at 12:21 pm

#174149

Hi,
I am developing an application using C# and SQL Server 2005. It's a service management system, and there are three types of customers. Residential, Commercial, and Educational. As such, there are three different tables to hold each respectively.
My problem is this. I was initially going to simply use Identity to generate a primary key for a new customer. But using Identity, I could easily have the same customer number for three different types of customers. This becomes an issue in the Service table, which holds all the service data for all types of customers. I could potentially have two different customers (of different types) who have the same number and are listed in this table.
To fix this, I was simply going to calculate my own primary key, by making the customer id a varchar consisting of a number followed by a letter (R for residential, C for commercial, and E for educational). My issue with this is that it could get screwed up if multiple users are entering a new customer. For example, if in the gap between calculating the new primary key and inserting the customer, another user tries inserting a customer, they will calculate the same primary key as the user which was in the middle of inserting. Then bang, duplicates.
Is there any way to assure that this won't happen? Or does anyone have any other ideas as to how I can do it?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

In this case it may be best to GUID's for the primary keys. You can generate these and assign them to a variable for inserting into a table by using NEWID().

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 2

ramses2nd, just for curiosity

how can you relate the customer , who is having different types

i.e, what happens If One customer belongs to two different types

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

If u want 1 customer to belong to more than 1 group then ur design has got to change.

Need to have "Customer" table, "CustomerType" table and "Customer_X_CustomerType" table to get that relationship.

Thanks

Sreejith

Adam Bagarella SSC Enthusiast Points: 154 More actions · Answer 4

I don't mind having a single customers table. The only problem is that there is different information for each type.

For instance. If I had all the customers in one table called Customers, then for all the Residential Customers, there would be a null value for Company Name, as only Commercial customers would have this. This could be a lot of wasted space if the number of customers grows high. This is why I split it up. Do you think this is acceptable?

Thanks

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

Rather I would suggest you to keep three different customer tables each for Residential, Commercial, and Educational types

with primary keys as

ResCUtomerID int identity(1,1) for Residential,

ComCustomerID int identity(1,1) for Commercial,

EduCustomerID int identity(1,1) for Educational

and add CustomerID column to each of these table with type int or guid

Create one table for mapping customerID's with ResCustomerID or ComCustomerID or EduCustomerID

with this design your CustomerID will be unique through out the Application

jezemine SSCrazy Eights Points: 8059 More actions · Answer 6

ramses2nd suggested above using a GUID as a primary key, and using newid() for the default. This is not a good idea if the pk is clustered since your insert perf will suffer.

If you are going to use a guid as a pk, you should use newsequentialid() as the default instead. This is only available if you are on 2005, which you should be since you are posting in a 2005 forum. Basically this function will return a new guid that is "larger" than the last guid so that insert perf doesn't suffer on a clustered pk. see http://msdn2.microsoft.com/en-us/library/ms189786.aspx for more info.

---------------------------------------
elsasoft.org

aureolin Hall of Fame Points: 3520 More actions · Answer 7

If you want to have a single key for each customer that can be used everywhere, then you'll need a separate customers table. Something like:

create table customers

(

customerid int identity(1,1),

customername varchar(100) not null,

customertype char(1) not null,

...

)

and then have a subtable for each type of customer, with a reference to the "customerid" as a foreign key.

Steve G.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Expanding on this... you could create 2 other tables for the extended information on the customer.

Another way of doing vertical partitioning .

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

Don't even think about GUID primary keys for this particular case, sequential or otherwise. There are other designs that will perform much better.

It doesn't really waste space to have a lot of usually-NULL fields in a table, although it is a bad design for other reasons. Unless you are causing page splits by going back and filling in fields that were originally entered as NULL, the cost of NULL fields is just one bit per row.

I agree with the last two posts, the best design is one basic Customer table with an int identity CustomerID, and related tables with addition fields for specific customer types.

Adam Bagarella SSC Enthusiast Points: 154 More actions · Answer 10

Thanks a lot for your help.

I'll be setting it up as the majority suggested. I'll create a sing Customer table which the rest of the application will interact with, making the customer type transparent. I'll have a different table for each customer type.

Thanks again.