Primary key issues

[Joan]

Hi,
I am looking at designing a new database for a company
that has several grain processing mills in several
locations. All of the ordering and billing is done at one
location for all mills. Currently the orders milled at
one mill begins with a certain number sequence and the
orders milled at a different mill begins with a different
number sequence. So at a glance of the order number they
can immediately tell which mill the order was milled at.
They would like this capability in the new system.
My question is how do I go about setting up the
primary key for the Orders table. Should it be a
composite key: one field of which is a 2 digit number for
the mill number and the other field is some sort of
sequential number for the order number? Is it possible
for half of a composite primary key to be an auto number?
How is the best way to set this up?

Joan

 

[Jeff Boyce]

Joan

I think you hit on a good solution -- one field to hold the mill ID number,
and one to hold your sequence number.

If you need to see the sequence number, and it needs to make sense to
humans, you probably DON'T want to use an Autonumber field. If you use
Google.com in the ms access group, you'll be able to find "custom
autonumber" postings with ideas for how to "roll your own" sequence
numbering routine.

 

[sajohn52]

The primary key is a unique identifier to allow you to select one recor
from your orders table. It can be made up of as many fields a
nessccary to make each record unique. One rule of thumb of whether
field should be part of the primary key is remove it and see if you ca
still find a unique row in the orders table?

From what you have written the mill ID certainly is importan
information for an order but shouldn't be part of the primary key an
really a foreign ket reference back to the miil table. Order ID is mor
than suffient as the primary key.

*Hi,
I am looking at designing a new database for a company
that has several grain processing mills in several
locations. All of the ordering and billing is done at one
location for all mills. Currently the orders milled at
one mill begins with a certain number sequence and the
orders milled at a different mill begins with a different
number sequence. So at a glance of the order number they
can immediately tell which mill the order was milled at.
They would like this capability in the new system.
My question is how do I go about setting up the
primary key for the Orders table. Should it be a
composite key: one field of which is a 2 digit number for
the mill number and the other field is some sort of
sequential number for the order number? Is it possible
for half of a composite primary key to be an auto number?
How is the best way to set this up?

Joan

-
sajohn5

 

[Joan]

Thanks, Jeff.

It's good to know that I am thinking along the right
path. Thanks also for the recommendation on "rolling" my
own sequence number instead of using an autonumber. I
will check on Google.

Joan