Re: Inventory Tracker Design issue
Sachin Singh wrote:
> I need to track: SUPPLIERS, SHIPMENT, ITEMS,
> SALE.
>
> The Issue is this lets imagine the ITEMS are
> products like Camera, memory, Hard disk,
> motherboard and other computer components. I want
> to track each item seperately as they can be
> defective, returned etc.
Is "Product" a particular type of good, and does Item represent a particular amount of Product instances. Is that correct?
>
> Now I can sell ITEMS by themselves or COMBINE them
> as a COMBO PRODUCT. So the relationships are:
>
> 1: SUPPLIER -------sends-------- SHIPMENT (N:1)
>
> 2: SHIPMENT --------has----------ITEM (M:N)
>
> 3: ITEM --------used to make----------PRODUCT
> (M:N) OR (N:1)
>
I'm not familiar with this notation. When I try to make english sentences of the cardinalities, entity names and relationship names, it does not seem to make sense. Your notation is in this format:
[ENTITY1]----[rolename]----[ENTITY2] ([cardinality entity1]:[cardinality entity2])
So, this would lead to these sentences:
1: (many) SUPPLIER(s) send (1) SHIPMENT
2a: SHIPMENT has (many) ITEM(s)
2b: ITEM shipped in (many) SHIPMENTS
etc.
Clearly, 1 does not make sense and 2b does not seem to either.
So, I'm misinterpreting your notation. Can you explain?
Often, it's better to state the problem in plain english, the cardinalities can be read from that. It's best to use two sentences: one that expresses the relationship of 1 instance of one of the entities to the other, and one sentence that does it the other way around.
Anyway, I think you are confused because you are mixing up products and items. Think of a product as a particular type of good ('IBM thinkpad r50p notebook') and an item as a particular instance of that good: ('that IBM thinkpad r50p notebook, on that shelf overthere'). For this problem, it's probably more practical to see an ITEM as a bunch of product instances, including the quantity: 2 crates of heineken premium lager (heineken premium lager = PRODUCT, crate is the shipment unit, 2 is the number of instances)
For you case I would get:
1a: 1 supplier sends 1 or more shipments
1b: 1 shipment is sent by exactly 1 supplier
2a: 1 shipment comprises 1 or more items
2b: 1 item is part of exactly 1 shipment
I'm not really clear on why you want with the 'used to make' relationship. Can you elaborate a bit on that?
tia Roland