A little OT but I thought someone might already know how to do this or refer me to a site that talks about the issue...

I am developing a new database for a client in FB 2.1. I am familiar with the normal commercial database where a vendor sells products like this:

Customer -
Order #25 - order item 1
order item 2
order item 3...

In this new case, the client takes an order from a customer with one or more order items and then ships them out on different invoices over a period of time and an invoice may have order items from various orders:

Customer -
Order #25 - order item 1 - Invoice #100
order item 2 - Invoice #101

Order #28 - order item 1 - Invoice #101
order item 2 - Invoice #101

Order #30 - order item 1 - Invoice #100
order item 2 - Invoice #102

So the physical design would be something like this:

Customer - Id, Name, Address etc.

Order - Id, CustomerId (FK Customer), Order_Date,...

Order Item - Id, OrderId (FK Order), Itemnum (FK Product), Cost, Qty_Order, Qty_Ship, Units, InvoiceId (FK Invoice?)

Invoice - Id, Invoice_Date, Invoice_Total, Terms, ShipVia,...

I think this will work from a FK standpoint, but I'm not sure if I am missing another obvious design solution. Any advice?

Regards,

Rick