Is my table design really done?

[Parts Manager]

Hello,

I have been working on this database table design for about a month or so.
Finally had some slower period here, so I proceeded to diagram it all out.
And with the help in these newsgroups, this one in particular, I believe I
might have my design finished.

However, when I try to use the Relationship screen where you relate all the
tables, the lines connecting all the tables get so convoluted that I can't
really see what goes where. I suppose if I was to drag it out to take a
couple of pages, it might make the lines more sensible, but instead I chose
to just put the designing of the tables in an Excel spreadsheet. By doing
this I could layout all the tables, the field names, which fields from other
tables will need to be related to that table, and really see if there were
any further breakdowns or tables that I needed to have.

Things we need to accomplish:
1) Our internal item number
2) Vendor internal item number
3) Many items per vendor, and many vendors per item
4) List of all items used per
boat/task/primary/secondary/major/minor/employee
5) List of all tasks used in a project and only those tasks used in the
project
6) Allow saving of prices historically (prices change from
purchase/invoice/next reorder
7) Compiled invoice of items used per boat. (Note: Not an accounting
system, the invoice being generated is so we have a record of what is used
per boat and we bill what has been used per billing cycle. A/P will
continue to use Quickbooks for invoicing, but they will use these generated
invoices for totals and listings of items used.
8) Totals/Reports of how much in man hours (forgot a field for hours in the
employee table - ooops@!), how much (dollars) in parts used so far, per task
for extra change orders, and to have a more clear picture for insurance
coverage of outgoing material and labor used per boat.
9) No plans to put accounting as a part of this database as of yet. There
is no tracking of inventory so no adjustment fields are needed for keeping
inventory counts.
10) Purchasing/Invoicing made easier for what we buy and what we use.

Those are some of the key elements for which I think I covered. The only
thing I see missing now that I write this is the need to add an Hours
field(s). I need to keep track of how many hours per boat/task/insurance
purposes.



Here is a link to the design I came up with;

Note: This link will not work until after May 4th @ 06:00 PM PST. I need to
upload from my home to our website after work.

http://www.howardmoe.com/ourtable.htm

Top 3 lines in yellow are the table description and table name.
Purple line with the word 'Done' in each column was just a placemark to let
me know when I did them all and everything was complete.

The light blue fields are fields that are used in a relationship to another
field. At the bottom of a column, if there are field names there, they are
what will go in the locations above where the link in blue is identified. I
wanted to show the link in blue so I know what to link to when I build the
tables in Access; yet have a real field name for it when it comes to that
point.

If you are inclined to review the tables and comment with suggestions or
mistakes, I am all ears. This is my first Access database so I am not
perfect.

Thank you,

Tim

 

[Lynn Trapp]

Tim,
I didn't follow your previous thread extremely closely since tina and Nikos
were helping you just fine. However, as I look at your table layout from
your link, I noticed a couple of things that you may want to address. These
may, or may not, be necessary depending on your situation.

1. I don't see any connection between your PO tables and your Invoice
tables. Your business needs may not require a match between these but, if
they do, then you will need to address that. How you do that will depend on
how you proceed on the following questions.

2. Will there ever be a scenario in which you might need to allow a vendor
to ship a given line item on a PO to different locations and/or on different
shipment dates? If so, then you will want to consider adding a
PO_Line_Shipments table that would be related many to one to your tblPOLine.
If you do this then your users would be able to designate different ship to
locations for each line item. They could also designate multiple shipment
dates for a given line.

3. Will there be a scenario in which you need to distribute the cost of a
given PO line (or shipment per the previous question) to different
departments or cost centers? If this is a possible need, then you would want
to add a PO_Line_Distributions table. This is where you would actually enter
information that would be trackable by your Invoice tables. If you don't
need multiple distributions, then you can record the invoice tracking
information in either tblPOLine or the suggested PO_Line_Shipments table.

For your situation, this all may be a bit of overkill but, since I support a
large enterprise Purchasing system, I thought I should bring up some things
that you may not have thought of. Let me know if you have any questions.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Parts Manager]

Tim,
However, as I look at your table layout from your link,
I noticed a couple of things that you may want to address.

1. I don't see any connection between your PO tables and
your Invoice tables.

True, I do not have a connection going between the two.

Our purchase orders are coded by "boat/task" and sent onto
accounting for when the invoices (A/P) arrive. The invoices
I speak of here, are for billing out for the items used per boat.
These invoices are for A/R. But all A/P and A/R is still using
Quickbooks. I don't believe I am smart enough to know
all the ramifications of adding an accounting functionality to
this database. Future I may try to work with Quickbooks
and how the files can act with each other, but for now, it
is not on the list to handle our day to day activities.

2. Will there ever be a scenario in which you might need to
allow a vendor to ship a given line item on a PO to different
locations and/or on different shipment dates?

Yes, there are times when I order quantities of a product,
but will request weekly or bi-weekly shipments. We have no
other facilities so another location is not necessary, but the
multiple shipments is something we do.

If so, then you will want to consider adding a
PO_Line_Shipments table that would be related many to one
to your tblPOLine. If you do this then your users would be
able to designate different ship to locations for each line item.
They could also designate multiple shipment dates for a given line.

I do like the part about having multiple shipments. I will review your
whole message again tomorrow so I can follow it thoroughly. My
plan, what we do now, is to have a RecQty field so that we can
order say 30 drums of a product, inform the vendor to send 6 per
week, then receive 6 per week in the purchase order. The Status
field can change from Active or Complete etc.... based on where
it is at. However, all forms of communication that we are to receive
the 6 drums per week is done verbally while ordering, and notes in
the purchase order on how we want it delivered. I will look at
our way and your suggested enhancement to see how to bring that
onboard.

3. Will there be a scenario in which you need to distribute the
cost of a given PO line (or shipment per the previous question)
to different departments or cost centers?

Actually, when we have multiple cost centers per line item on an invoice,
our purchase order has it broke down by line. Meaning that I may
order 20 total of a product to a vendor and get an invoice for 20, yet
we will have to purchase order line items of 10 each with the associated
cost center noted for each line.

For your situation, this all may be a bit of overkill but, since I
support a large enterprise Purchasing system, I thought I should
bring up some things that you may not have thought of. Let me
know if you have any questions.

And I appreciate the time you took to review my tables Lynn. I am
after any input to make sure I am set to go on the next phase of
this database. I will definetly consider how the above suggestions
can or do work into our setup we have here. The one that I can
think of above is the multiple shipment as we do this now. I just
hadn't thought there may be an alternative to how we do it.

Thank you,

Tim

 

[Lynn Trapp]

Our purchase orders are coded by "boat/task" and sent onto

accounting for when the invoices (A/P) arrive. The invoices
I speak of here, are for billing out for the items used per boat.
These invoices are for A/R. But all A/P and A/R is still using
Quickbooks. I don't believe I am smart enough to know
all the ramifications of adding an accounting functionality to
this database. Future I may try to work with Quickbooks
and how the files can act with each other, but for now, it
is not on the list to handle our day to day activities.

At the very least, then, you may want to go ahead and add a PO_Id field to
the invoice tables (probably the line level) just for possible future
changes. Then you don't have to worry about it later.

I do like the part about having multiple shipments. I will review your
whole message again tomorrow so I can follow it thoroughly. My
plan, what we do now, is to have a RecQty field so that we can
order say 30 drums of a product, inform the vendor to send 6 per
week, then receive 6 per week in the purchase order. The Status
field can change from Active or Complete etc.... based on where
it is at. However, all forms of communication that we are to receive
the 6 drums per week is done verbally while ordering, and notes in
the purchase order on how we want it delivered. I will look at
our way and your suggested enhancement to see how to bring that
onboard.

I think you will be happy with yourself if you go ahead and add the
shipment level to your PO's



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html