Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Should i be using a composite key?

Posted on 2006-11-24
Medium Priority
654 Views
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?
0
Question by:ethnarch
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 10

Accepted Solution

kacor earned 800 total points
ID: 18010579
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

LVL 2

Author Comment

ID: 18011156
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

LVL 10

Assisted Solution

kacor earned 800 total points
ID: 18011425
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

LVL 2

Author Comment

ID: 18011702
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

LVL 2

Author Comment

ID: 18011901
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

LVL 19

Assisted Solution

NickUpson earned 600 total points
ID: 18011941
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

LVL 2

Author Comment

ID: 18012005
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month8 days, left to enroll