Solved

Should i be using a composite key?

Posted on 2006-11-24
7
601 Views
Last Modified: 2013-12-09
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
Comment
Question by:ethnarch
  • 4
  • 2
7 Comments
 
LVL 10

Accepted Solution

by:
kacor earned 200 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

by:ethnarch
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

by:kacor
kacor earned 200 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 2

Author Comment

by:ethnarch
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

by:ethnarch
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

by:NickUpson
NickUpson earned 150 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

by:ethnarch
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now