Advise on table structure

[Jan Il]

Hi all - Access 2000 - Windows ME

We have recently began using a new accounting program, which is very
burdensome if and when it is up and running. There is now a myriad of
'chains of authorization' that a simple purchase requisition must now go
through before it can even get to purchasing. Our department head is the
only person in our department with authorization to review any of the
accounting information, and they are rarely in the office for any length of
time. Therefore, once the purchase requisition is sent electronically into
the abyss of the network, we have no idea where it is, or what the status of
it is at any given time. Thus, I am trying to set up a simple Access db to
track the PR's at the time they are created by our staff.

The fields for the table thus far are
tblPRTracking
PRID - Autonumber
PRNo - Data type number (Standard) - PK
PRDate - Date/Time data type
Vendor - Text data type
PRType - Text data type (for Open or Work Order)
CostCtr - Number data type (Standard)
WONo - Number data type (Work order requiring PR)

Additional tables are:
tblAccountID
AcctID - Autonumber
AcctNo - Number data type - PK

tblDept
DeptID - Autonumber
Dept - Text data type - PK

tblPOrder
POID - Autonumber
PONo - Text data type - PK (data type, as there are alpha and numerical
combinations in the PO numbers)

Now, here is where I am a bit confused on this. Due to the new system, we
will have to create Standing PR's and Work Orders for parts acquisition from
our Stores dept. for the various areas of equipment in our dept. crossing
gates, signals, switches, etc. for both Track and Wayside Departments that
will be kept 'open' over the current FY. For purchases from outside Vendors
that we use on an on-going basis, we will have to create Standing (Open)
PR's for the FY as well. Then, Purchasing will open a Standing Purchase
Order for the FY. This will, hopefully, mean that when new work order is
submitted referring to the PO number and a cost center, it will mean that
hundreds, or even thousands, or PR's will not have to be created and
submitted. As the new program incorporates three agencies, this would
totally swamp the system, and accountings ability to function properly.

So, ultimately there can;
1) Multiple Work Orders created against Standing PR's.
2) Multiple PO's created for a PR (to speed up authorization)
3) Multiple PR's created against Standing PO's

I just want to know if I have my tables set up properly. I would truly
appreciate any suggestions regarding the setup of the tables, and any
changes or additions that might be needed that I have not thought of or that
may not be necessary. I have reviewed the Help files, and done some
experimenting with various setups, but, as I have never dealt with this type
of process before, I'm not sure, and want to start out right. This db will
most likely be requested for use in other departments, as they are on the
same leaky raft we are, so I want to make sure that it will work properly
for all depts.

Sorry this is so long, but, I wanted to cover as much information as
possible to, hopefully, make it easier to understand what I need, and am
trying, to do.

Best regards,
Jan



--

 

[TC]

You may need to review your understanding of "primary key" (PK).

For example, you say:

tblDept
DeptID - Autonumber
Dept - Text data type - PK

If Dept is indeed the primary key, then, you do not need the autonumber.
Conversely, when a table has an autonumbver, the purpose of that field
(generally) is to act as the primary key, when no other field(s) will do.

I imagine that "Dept" the department name (eg. "Purchasing"), and DeptID is
the unique # by which you want to identify that department in other tables.
If that is so, then, DeptID is the primary key (& should be defined as such
in the table). Dept might well be unique, but, in this case you've chosen to
add an autonumber, so that field should be the primary key (it is smaller &
less subject to change).

HTH,
TC

 

[Jan Il]

Hi TC!

You may need to review your understanding of "primary key" (PK).

For example, you say:

tblDept
DeptID - Autonumber
Dept - Text data type - PK

If Dept is indeed the primary key, then, you do not need the autonumber.
Conversely, when a table has an autonumbver, the purpose of that field
(generally) is to act as the primary key, when no other field(s) will do.

I imagine that "Dept" the department name (eg. "Purchasing"), and DeptID is
the unique # by which you want to identify that department in other tables.
If that is so, then, DeptID is the primary key (& should be defined as such
in the table). Dept might well be unique, but, in this case you've chosen to
add an autonumber, so that field should be the primary key (it is smaller &
less subject to change).

I'll go back and reconsider what you have suggested. There are only two
departments in our area, Track and Wayside, but each dept has several const
center numbers, so that is why I added the field Dept. When the PR, Work
Order or PO is issued, the cost center will define the department, but, in
order for us to be able to filter the records for the two departments, I
added the Dept field, which will be related to the cost center for each Dept
from the tblPRTracking.

Thus, if I understand you correctly, if I am going to use the Dept as the
PK, then I do not need the DeptID with an autonumber? Or, If I am going to
have the autonumber field, then I should use it as the PK and don't need the
Dept field? And, this would apply to all the other tables as well?

Hmmm...clearly I need to re-evaluate my understanding of all the
instructions and references regarding table structure. Obviously, my
understanding is way off base. As, I understood that, under normal
circumstances, there should always be an autonmumber field, and, normally,
it is not used as a primary key, except under certain circustances, such as
you mentioned, when no other field will do. But that, normally, the
autonumber field would not be used as a PK.

Thank you very much for your time to reply and assist with this issue, TC, I
truly appreciate it.

Best regards,
Jan

 

[John W. Vinson]

Hmmm...clearly I need to re-evaluate my understanding of all the
instructions and references regarding table structure. Obviously, my
understanding is way off base. As, I understood that, under normal
circumstances, there should always be an autonmumber field, and, normally,
it is not used as a primary key, except under certain circustances, such as
you mentioned, when no other field will do. But that, normally, the
autonumber field would not be used as a PK.

You DO need to re-evaluate!

If you have a "Natural Key" - a value that is unique to the record,
and preferably stable and short - then that should be used as the
Primary Key. (For instance, people's names do not qualify as a Natural
Key by themselves, since they are not unique, not stable, and aren't
as short as a Long Integer).

If you have an autonumber at all, it will usually be the Primary Key -
a "surrogate key" used because there is no convenient natural key.

There's differences of opinion among developers whether big, complex,
multifield natural keys require the use of a surrogate. My friend Tom
Ellison argues convincingly that you should always use a natural key
if possible - for instance, in a people table, something like
[LastName] + [FirstName] + [MiddleName] + [Suffix] + [Address] + [Zip]
would usually suffice to uniquely identify a person. That may be an
awful lot of data to carry through to multiple other tables, though,
so most developers would put a unique Index in these fields, to
prevent duplicates; and add an Autonumber as a surrogate primary key
so that links to other tables can be made on a concise Long Integer
rather than on six largish text fields.

John W. Vinson [Access MVP]

 

[Jan Il]

Hi John!

Hmmm...clearly I need to re-evaluate my understanding of all the
instructions and references regarding table structure. Obviously, my
understanding is way off base. As, I understood that, under normal
circumstances, there should always be an autonmumber field, and, normally,
it is not used as a primary key, except under certain circumstances, such as
you mentioned, when no other field will do. But that, normally, the
autonumber field would not be used as a PK.

You DO need to re-evaluate!

If you have a "Natural Key" - a value that is unique to the record,
and preferably stable and short - then that should be used as the
Primary Key. (For instance, people's names do not qualify as a Natural
Key by themselves, since they are not unique, not stable, and aren't
as short as a Long Integer).

If you have an autonumber at all, it will usually be the Primary Key -
a "surrogate key" used because there is no convenient natural key.

There's differences of opinion among developers whether big, complex,
multifield natural keys require the use of a surrogate. My friend Tom
Ellison argues convincingly that you should always use a natural key
if possible - for instance, in a people table, something like
[LastName] + [FirstName] + [MiddleName] + [Suffix] + [Address] + [Zip]
would usually suffice to uniquely identify a person. That may be an
awful lot of data to carry through to multiple other tables, though,
so most developers would put a unique Index in these fields, to
prevent duplicates; and add an Autonumber as a surrogate primary key
so that links to other tables can be made on a concise Long Integer
rather than on six largish text fields.

John W. Vinson [Access MVP]

Well... table structure has always been somewhat of a
poser for me, sorta like Algebra, trying to get all the signs
straight. Easy for some, harder for others. <g>
Guess I've just been lucky thus far, in that, it seems
I've actually been doing it right most of the time, in
that I have wound up using the autonumber as the
primary key in most of my tables. Mostly because, none
of the other fields in the tables seemed fit as a primary
key, but, not fully understanding why.

It just seemed the Purchase Requisition was the main
entity in the process in this case, as it seems every
step along the way is in some way based upon the
PR number, and all other actions are ultimately related to,
and dependent upon it. It would seem that, due to lack of
proper understanding, I was just making it more
difficult then it needs to be.

Best I start from scratch, both between my ears,
and these tables. ;-))

Thank you so much for your time and additional
explanation, John, I truly appreciate it.

Best regards,
Jan

 

[John W. Vinson]

It just seemed the Purchase Requisition was the main
entity in the process in this case, as it seems every
step along the way is in some way based upon the
PR number, and all other actions are ultimately related to,
and dependent upon it.

Sounds like a good Natural Key to me! I'd just use it, and drop the
autonumber (and the foreign keys linked to the autonumber, if any).

You're doing better than you think you're doing! <g>

 

[TC]

Hi Jan

I can't remember if I suggested this, but if not, you may like to read the
following article on database normalization:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH
TC

 

[Jan Il]

Hi TC!

Hi Jan

I can't remember if I suggested this, but if not, you may like to read the
following article on database normalization:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

No, you didn't, as I don't see it mentioned in your original response.
Thank you for thinking to add this site information, it is very helpful. I
can't get to the webcast as it creates an error and Explorer shuts down,
but, I'll try again later.

Thank you so much for all your time and assistance, I really do appreciate
it.

Best regards,
Jan