Thoughts on database design for order tracking system

J

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
 
D

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.
 
G

gllincoln

Hi Jim,

If I understand your needs - you want to be able to quickly see the current status of requests and current credit lines in place, on demand.

You mentioned reports - but it seems to me that the core of what you want to see would be well served with a couple forms and subforms. maybe a couple popups.

The first one might be client centric - the main form would show whatever fields you wanted regarding one client at a time.
In the header of the main form, you might want to put a few unbound text boxes and a search button. These would allow the user to input (for example) maybe the first part of a company name (Allied) for instance when they were looking for Allied Chemical & Some Other Stuff Inc. Or a contact first or last name, or a client ID # or perhaps area code or complete phone.
Then filter the client recordset down to those records that matched. Thus if you put in John as the contact name, you might have 4 or 5 records in the filtered set, whereas putting both first and last as in

Contact Info
First: John Last: Whackenheimer

would probably only return one record.

This gives you easy search capability.

The subform (in my concept) would be the credit lines - we could have a continuous form listing each supplier/creditor, amount, and status.
Double clicking the supplier ID could bring up a pop up form offering a datasheet transaction log showing each step of the processing to date for this client/supplier pair.

Probably be nice to have a form that made keyboarding a transaction result (approval received, paperwork received, request sent, whatever) easy and efficient.

The entry form should let the user easily select a client - select an existing transaction to update or initiate new credit requests.

Might use a tab control to offer a couple different views/subforms, depending on what type of action the user selected. If updating an existing transaction, we let the user double-click the transaction they want to update, then pop up a form that carries the known data and let's the user add the change info to create a new transaction record and we use code to update the status as appropriate. If new credit request, be cool to let the user enter multiple requests in one batch, maybe use checkboxes - to select suppliers from a list of suppliers, enter an amount, the date - have this on a different tab.

The second form would be supplier centric, have a similar type search facility in the header of the form, and a sub form would be a list of clients showing status amount, last change date. Double clicking the client could bring up a transaction log for the supplier/client pair.

Reports should probably include a list of credit apps by stage of processing and some transaction aging.

Might build a followup required query as a popup recordset to show any transactions beyond nn days aging. (for instance) Waiting for approval, or paperwork over ten days, or whatever.

Maybe build a switchboard to let the users navigate between forms, see that followup required recordset, pull up the reports.

All you really need to add is a transaction table to your client and supplier tables. I might also create a user login and track who entered what transaction.

The transaction table would include the client id and supplier id as foreign keys, a timedate stamp, a pulldown combo box with the type of event, a short text field for comments (no more than 40 or 50 chars - tell 'em brevity is the soul of wit.) and maybe a user id col to track the data entry person.

As a finishing touch, you might want to build maintenance forms to let the user edit/add new clients and suppliers.

Hope this helps,
Gordon
 
J

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top