What tables do I need?

[VT]

I am setting up a database to help track assets that we use as demonstration
stock. We lend these goods to prospective customers. Whenever one or more of
these goods go out to a customer, they are accompanied by a pro-forma
invoice.

Am I right in thinking that I should set up:
a table listing the goods,
a table listing customers,
a table listing the pro-forma invoices, and
a junction table to detail the goods on each pro-forma?

Secondly, our customers are usually overseas and our products usually need
export documentation. I need to link every product to the export document or
documents they require to avoid trouble at Customs here and overseas. How do
I do that?

Many thanks

VT

 

[Klatuu]

Typically, in invoicing systems, an invoice is made up of two tables. The
Invoice Header and the Invoice Detail. The Header contains the information
pertinent to an Invoice (Invoice Number, Date, Customer, Ship To, Bill To,
etc) The Detail contains information about the goods (Invoice Number,
product code, product description, qty ordered, qty shipped, unit price, etc)
Join these tables on the Invoice Number.

The Invoice would be related to the customer by the customer number in the
Invoice Header. The Invoice Detail table would be related to the inventory
table by item code.

As to the customs docs required, having been involved in such an application
for a major international shipper, the way we did it, since customs
requirements are mostly based on the type of goods, we had a table of type of
goods that had a child table that listed all the documents taht belonged to
the type. Each inventory item then had a field that identified it's type.