Thoughts on database design for order tracking system

[Jim Armstrong]

I am creating a small credit tracking database for my company and am
having a bit of trouble planning the design.

The workflow would be:

A client requests credit approval from our suppliers. Currently, we
have five suppliers and this may grow or shrink moving forward.
Typically, a client will request from all five simultaneously, but
there are instances where the client may not want to work with a
specific supplier.

So - Client ABC requests $100,000 credit line from Supplier 1,
Supplier 2, and Supplier 5.

Then, the approval process must be tracked. Each supplier will come
back either declining or approving the request. If approved, documents
must be sent to the client and received back signed. So, the possible
"order status" types would be:

Requested
Declined
Approved
Documents Requested
Documents Open

Right now, there are two types of reporting we'd want to use. The
first report would be: "For client ABC, show me the current status of
all requests" and this report would spit out: Supplier 1 = Declined,
Supplier 2 = Not requested, Supplier 3 = Approved, Supplier 4 =
Approved, Suppllier 5 = Docs requested, etc...

The second report would say, "For Supplier 1, show me all client
credit lines" and would return Client ABC = Approved, Client DEF =
Declined, etc..."

I've already created a clients table and a suppliers table with all
the contact info for those parties. My confusion lies in how to
construct the main orders table(s) and keep tabs on the request
status.

Looking at inventory/order tracking DBs, I see some utilize an orders
table and a separate "tracking" table to keep tabs on shipping status.
So one thought is when a customer requests approval to treat it as an
order (using an auto-gen'd order # as a PK), then link to that PK from
a tracking table where the status could be updated. Reports could pull
the order info (ABC requested $100,000 from Supplier 1 on 3/11/08) and
pull the status from the tracking table (Supplier 1 declined them on
3/12/08)

Does this seem like a good way to keep track of this info? I want to
make sure that it is always easy to get the most current status of
each request... I imagine this is very similar to customer
relationship databases where something like sales opportunities are
tracked from beginning (cold called client) to the end (customer order
placed) - just never designed a DB where status needed to be tracked,
and not sure what best practice would be here...

Thanks for any clues anyone can provide. Not looking for someone to
design it for me, just some pros/cons on different designs you guys
might have seen in the field.

Thanks much,

Jim

 

[DM]

Recommend, if you company can afford it and don't have it, buying
SharePoint, send you to school to learn how to administer it and then once
you know how all this could be done through sharepoint, start the process.

Access can be a starting point but SharePoint has much more capabilities
along the lines of your needs.

 

[Jim Armstrong]

Gordon -

Thanks, that did help. I started piecing it together and looks like
I'm on the right track - thanks for the assistance.

One other quick question - obviously, I want to keep a transaction
history for each event in the lifecycle of one of these transactions -
from what I've read, there appears to be two schools of thought on how
to keep a history or audit log. The first approach would be to build a
separate tblHistory and when a record in the main table is changed,
copy the pre-change record to the history table and then make the
change in the main transaction table.

The second approach would be to keep all records in one main
transaction table, and use a flag to indicate whether a record is an
old archive record or a "live" current record.

It seems to me the separate table idea would be cleaner, but was
wondering if anyone had thoughts on the pros/cons of either method.

Thanks, as always...

-Jim

 

[mark]

Gordon -

Thanks, that did help. I started piecing it together and looks like
I'm on the right track - thanks for the assistance.

One other quick question - obviously, I want to keep a transaction
history for each event in the lifecycle of one of these transactions -
from what I've read, there appears to be two schools of thought on how
to keep a history or audit log. The first approach would be to build a
separate tblHistory and when a record in the main table is changed,
copy the pre-change record to the history table and then make the
change in the main transaction table.

The second approach would be to keep all records in one main
transaction table, and use a flag to indicate whether a record is an
old archive record or a "live" current record.

It seems to me the separate table idea would be cleaner, but was
wondering if anyone had thoughts on the pros/cons of either method.

Thanks, as always...

-Jim