Should i be using a composite key?

I have been developing some software for awhile now and i keep using composite primary keys on my tables.  Through my learning process i have come across a lot of people saying that composite keys should be avoided unless absolutely necessary.

So i have gotton to a point where maybe it's time to change my database design before i move forward and begin implementing this.  

Here is what i am working with, it's basically an inventory tracking software which can work at multiple locations polling data back to a central location.  Lets take the orders table as an example

Currently i have a table setup like this
CPK Indicates Composite Primary Key
FCPK Indicates a Foreign Composite Primary Key
Orders
   OrderNo Integer -CPK
   LocationNo Integer -CPK
   DateCreated TimeStamp
   .......

OrderItems
   OrderNo Integer -CPK -FCPK
   LocationNo Integer -CPK -FCPK
   LineNo Integer -CPK
   Description VARCHAR
   .....

What i have been thinking of changing this to (keeping in mind problems i may encounter of duplicate items when polling data between locations)
Also i thought possibly to make checking for duplicates easy i could create a column which combines the old Composite Primary key into one column for instance using a formula like this to create a unique number
(OrderNo *1000) + LocationNo = UniqueOrderID
(OrderNo *1000000) + (LocationNo * 100) + LineNo = UniqueItemID

Orders
   UniqueID Integer -PK
   UniqueOrderID Integer
   OrderNo Integer -FK
   LocationNo Integer -FK
   DateCreated TimeStamp
   .......

OrderItems
   UniqueID Integer -PK
   UniqueItemID Integer
   OrderNo Integer -FK
   LocationNo Integer -FK
   LineNo Integer -CPK
   Description VARCHAR
   .....

so basically the uniqueID would be different at all locations but the second uniqueID created from the formula would match at all locations and i could use this to check for duplicates or just prevent them from occuring by restraining them as unique.  Does this sound logical or am i complicating things?
LVL 2
ethnarchAsked:
Who is Participating?
 
kacorConnect With a Mentor retiredCommented:
How would you like to get the original order number which serves as base for all composite key calculations?

Where are coming this orders from? They are company's intern orders or you get from extern companies?

If you'd like to be undependent from the client's (the own company can be "client" in this meaning) order numbers you have to use a generator to generate unique order IDs (let it be NewOrderNo) and use  a column to store the original better said the client's order number. (The client ID must be stored somewhere in this table to identify the orderer company). This NewOrderNo can serve ase base for your next operations.

Using this principle you can later combine at your pleasure your ideas I mean. I know only the submitted points of view of your project therefore I can't tell more about.

wbr
kacor
0
 
ethnarchAuthor Commented:
The orders are created internally at the company, however they could be created either remotely at the location in which the orders are going or they can be created at the corporate or central location.  I because of the problem with numbers overlapping i have been thinking that the corporate location possibly could generate negative  or temporary order numbers so that the remote location when it receives them could then determine if this order is negative and give it positive number reassigning the orderno.

Also the orders are actually purchase orders so maybe it is better to look at them in that context.  They are products that the company is ordering.
0
 
kacorConnect With a Mentor retiredCommented:
In this case you have to make possible on the continously running central server that each of order (purchase) numbering places must take the unique number from this central table the order/purchase number generated by a generator. The generators work perfectly in multiuser environment giving everytime positiv integer number and you have nothing to do with this numbers. Of course you can give them a condition for example each year reset this order number to 1 or 10001 or similar, what depens on your used rules.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ethnarchAuthor Commented:
I understand what you mean, i think though it's my fault i left out a detail.  

The problem is that the remote locations and the corporate or central location all have different database servers so each remote location has it's own server and the same with the central location.  The Orders are in fact polled using say a .csv file and then they are zipped up and sent over the internet.  Where the receiving location will then import the data from the .csv file. So the corporate nor the remote locations would know what the current generator number is except at it's location.

I had never considered resetting the order numbers back to 1 however that seems like a good idea for keeping the numbers low so they don't take up a lot of room in the database.  However then the order number can't be unique either.

Thankyou for the help by the way I do appreciate it
0
 
ethnarchAuthor Commented:
the other problem that i have is that each location should have it's own set of order Numbers.  I was thinking about using a generator however there is no way to generate a seperate series of Order numbers for each locationNo
0
 
Nick UpsonConnect With a Mentor Principal Operations EngineerCommented:
you can set the generators on each site to a different number before they are used, so haed office maybe has them set to 0, the first remote office would be set to 1,000,000 say
0
 
ethnarchAuthor Commented:
i think i have this all worked out now hopefully it works in practice thankyou both for the help i will increase the point values for both of your inputs on this thankyou very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.