Post reply

Calculating A Primary Key VS Idenitity

  • 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

  • September 7, 2006 at 1:08 pm

    #659188

    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().

  • September 7, 2006 at 1:52 pm

    #659191

    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

  • September 7, 2006 at 2:22 pm

    #659199

    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

  • September 7, 2006 at 2:35 pm

    #659206

    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

  • September 7, 2006 at 2:59 pm

    #659216

    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

     

  • September 7, 2006 at 11:42 pm

    #659263

    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

  • September 8, 2006 at 9:53 am

    #659381

    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.

  • September 8, 2006 at 9:56 am

    #659386

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

     

    Another way of doing vertical partitioning .

  • September 8, 2006 at 3:52 pm

    #659477

    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.

  • September 11, 2006 at 8:44 pm

    #659792

    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.

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

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