Database design for tracking tracking deliveries against orders

[DevelopV]

I need to track deliveries from suppliers against purchase orders.
i.e I generate a purchase order on a supplier. The supplier may deliver:
* all products in one delivery
* products over a couple of deliveries
* may substitute product with an alternative

I am racking my brains on how to set the tables and the forms.

Can anyone suggest the correct structure for this? Perhaps know of a sample database to get my grey matter working?

Thanks

 

[timfoster]

In the simplest format I would suggest:
orders >- orderitems
deliveries >- deliveryitems
items

This will enable to you record the orders as they are placed along with the items ordered. When deliveries come in record these as well with the items delivered. Presuming your delivery notes have an indicator that identify which item(s) they are replacing, you should be good to go. I'd be tempted to have the code add new delivered items to the table if this isn't a definitive list.