Hi there,
I am designing a database for inventory management and can't get my
head across a design issue.

In this database I want to track the following entities:
SUPPLIER - person, organisation sending me shipments
SHIPMENT - One single package received, from one supplier, can contain
multiple products in multiple quantities.
PRODUCT - mostly electronic products like memory, camera etc.
SALE - transaction when a customer buys Products
CUSTOMER - person, organisation who buys our PRODUCTS.

THE PECULIARITIES:
I can get let say 100 Piece of a particular camera and 200 pieces of
memory sticks, the business problems are:

1: Every shipment might have different prices for same product and
they fluctuate. Therefore there is a need to identify each individual
ITEM (a single camera)and its costs.

2: Second problem, requiring to track each piece or item is that some
pieces might be returned, faulty or damaged.

3: Lastly we can also combine 2 or more products to form a combination
product - example:- One camera PLUS 2 memory stick can be sold as a
Products Digital camera combo.

THESE are the relationship I am ok with:
SUPPLIER ---------send-----------SHIPMENT (1:N)
SHIPMENT -------contain----------ITEM (N:M)

SALE ----------is for----------CUSTOMER (N:1)

THE QUESTION:

As indicated above I cannot figure out the three points:

1: How to keep track of each item NOT PRODUCT TYPE.
2: HOW to keep track of COST of item - Each piece if they arrive on
different shipment?

Thanks
SS

I once wrote a shop system using the following technique

[Product code] [Delivery Batch] [Item No]

The codes got potentially rather long, but with 'assisted input' it
was quite viable.

Nowadays I would go for :-  ABCD / 1296 / 392

Ironically that system was the tag end of a much larger project, and
the shop in question burnt down before my code got a real testing.

In your case the shipment should relate back to an order, and that
should keep the costs. However controversially I am not averse to data
duplication (de-normalization) so one could keep a record for every
item.

You could create something like a bland number for each item and store
a complete description of its :-
    [Product Code][Order No][Delivery Batch][Price]

There is quite a lot to be said for making unique IDs mean absolutely
nothing in themselves.

One thing I really would do is to keep a separate list of orders and
deliveries and sales in a sort of XML format. Disk space is dirt
cheap.