Post reply

Redesign Help - Composite Keys

  • January 19, 2006 at 10:15 am

    #364768

    I'm in the process of redisgning our company's db.  The previous designer did a poor job assuring data integrity.  As it stands now, I have a transactions table that also includes the customer's personal information.

    One of the columns in this table is Customer_ID  (user defined) .  My goal is to remove all of the Customer data (name, address, etc) from this table and insert it into the Customers table.  The problem is that the same CustomerID exists for those with a different name.  In order to make a primary key on my Customers table (currently there isn't one), I would have to use three columns:  CustomerID, CustomerName, and CompanyID.  Are there any disadvantages to this? 

    The other alternative is to insert the existing data from my Trans. table into Customers and then use an existing identity column. 

  • January 19, 2006 at 10:37 am

    #615859

    I think that if the CustomerID is unique within CompanyID you could use the two as your key. If you have to throw in the CustomerName into the mix I think you should go with the Identity.

    Personally I would go with the Identity anyway so all the linking is done on one field,  but since I don't know the full impact of the changes it is hard to evaluate.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 19, 2006 at 3:46 pm

    #615951

    I have been in this situation twice.

    The best thing to do as Jacek0 has suggested is to use the Identity Column. Once you have done that the painful process begins where you will want to delete the duplicate record but before you do you will have to assign all the foreign keys to the Identity Column that will now be the Primary Key.

    Kindest Regards,

Viewing 3 posts - 1 through 2 (of 2 total)

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