On Thu, 27 Aug 2009 10:04:01 -0700, erinb-tsc
Comments inline.
Thank you your intital help was very helpful and thought provoking.
Glad to be of help!
If you don't mind thought I would run by you the break down of the tables I
have come up with. Would LOVE your prespective and advise.
Company Table
Company ID (Primary)
Company Name
Avoid using blanks in fieldnames: CompanyID, CompanyName. They're legal but
can be a real hassle.
Company Address Table
Company Name (Create relationship between Company and Company Address)
Don't put the company *name* in this table - put the CompanyID instead.
Address ID (Primary)
Address Type (Billing/Shipping)
Address
Address 2
City
State
Zip
Company Contacts Table
Contact ID (Primary)
Company ID (Create relationship)
Last Name
First Name
Position
Again leave out the blanks. You may find yourself wanting a few more fields
but this is a good start.
Product Table
Product ID (Primary) (Part Number)
Description
Orders Table (Orders are from Dealers drop shipping to end customers, need
to track who, what, where and when)
Orders ID (Primary)
PO Number
Company ID (Bill To) (Create relationship?)
Company ID (Ship To) (Create relationship?)
Absolutely. I'd name the fields BillTo and ShipTo - parentheses in fieldnames
are a BAD idea.
Order Date
Address ID (Bill To) (Create relationship?)
Address ID (Ship To) (Create relationship?)
Contact ID (Bill To) (Create relationship?)
Contact ID (Ship To) (Create relationship?)
These may be redundant with the CompanyID - since you can look up the company
if you know the address (linking from Orders to Addresses and from Addresses
to Company) you may want to omit the CompanyID's in this table altogether.
If each company had only one address (or only two, ship-to and bill-to) you
could just include the addresses in the company table, but I'm sure that does
not reflect the business reality!
Order Detail Table
Order ID
Product ID (Subform?)
Quantity (Subform?)
Sales Amount
Freight
Total
A field isn't a subform! You would almost surely *USE* a subform (based on
OrderDetails) to enter data into the table, using a combo box to select the
product (this will let the user see the human-meaningful product name while
storing the computer-relevant ProductID). See the Northwind sample database's
Orders form for precisely this scenario.
Invoice/Shipment Table
Invoice ID (Primary)
Order ID (Create relationship?)
Invoice Date
Company ID (Create relationship?)
I'd separate these. You might have many shipments and only one invoice, or
many of each, not necessarily tied to one another.
Commission Table (Payments)
Invoice ID (Create relationship?)
Order ID (Create relationship?)
Sales Amount
Commission Rate ID
Commission Amount Due
Commission Amount Paid
Date Paid
Does a commission in fact relate *to a single invoice?*
Commission Rate Table
Commission Rate ID (Primary)
Comission Rate
Please feel free to comment freely. Trying to create the best database first
time around, know this isn't always possible, but one can *hope*. Some of
these table are will be made into forms and numerous query will hopefully be
run to track payments, receivables and sales charts.
Clarify:
Tables will NOT EVER be "made into forms".
Tables store data. They stay under the hood. Users should basically NEVER see
a table datasheet. You would develop Forms (many of them with subforms and
other tools) based on the tables, and the users would interact with the data
via these forms. You'll also develop Reports to print out (or provide a static
display) of data; charts can appear on either forms or reports, and may need
additional software such as Excel to produce them - charts and graphs are one
of the weaker features of the program.
Thanks again for your previous help, looking forward to your response.
You've bit off a large and complex project, but you've already clearly gotten
the principles well in hand! Good luck!
