[cassiopeia]

Im aware of this link, and I have read it. but I still confuse with how to define primary key in inventory table. I know that my inventory table still wrong, coz it can not store same item with different serialnumber.

Item
Item_ID (PK)
Category_ID (FK)
Merk_ID (FK)
ItemType_ID (FK)
Unit_ID (FK)

Inventory
Office_ID (PK)
Warehouse_ID (PK)
Item_ID (PK)
SerialNumber (UNIQUE)
Barcode
BatchNumberId (FK)
Quantity

and then im thinking about separating inventory in to two table :

Inventory
Office_ID (PK)
Warehouse_ID (PK)
Item_ID (PK)

Inventory Detail
Office_ID (PK)
Warehouse_ID (PK)
Item_ID (PK)
No (PK)
SerialNumber (UNIQUE)
Barcode
BatchNumberId (FK)
Quantity

I just not very sure that was the best approach. So I would like to have some idea how to design it properly.

My assumption : every item should be stored in certain warehouse, and the warehouse belong to certain office. Same item could have only one serial number.

Any idea, suggestion, further reading, or design correction, or another approach, I would really appreciete it.

thank you

This post has been edited by cassiopeia: 26 March 2012 - 02:20 AM

[cassiopeia]

found the answer.

case closed

[e_i_pi]

Just food for thought here... if you want a 1-to-1 relationship between Inventory and Inventory_Detail, you needn't use the same PK identifier in both tables. In fact, that goes against the principles of normalisation (though is still entirely valid data layout).

Considering the second example only, I would opt for this:

Inventory
ID (PK)
Office_ID (FK)
Warehouse_ID (FK)
Item_ID (FK)

Inventory Detail
Inventory_ID (PK)(FK -> Inventory.ID ON UPDATE/DELETE = CASCADE)
No (PK)
SerialNumber (UNIQUE)
Barcode
BatchNumberId (FK)
Quantity

Using this schema you are achieving a better level of normalisation, and thus decreasing the chance of data corruption during partial (read: failed) updates/inserts/deletes.

[cassiopeia]

hi, thanks for reply

e_i_pi, on 16 April 2012 - 07:02 PM, said:

you needn't use the same PK identifier in both tables. In fact, that goes against the principles of normalisation (though is still entirely valid data layout).

i think i could not agree, becoz i need to differentiate between stock base on office and warehouse. more reason is this article. althoug, i also very much aware of your opinion base on this article.
(but i admit, i could possibly end in a wrong conclusion since im not very good in english )

in the end, i dont use both design i mention above. i think i gonna use SKU (Stok Keeping Unit), coz at the moment i think its much better and much easier to maintain in the future (hopefully )

and thank you for replying

-cassiopeia-

This post has been edited by cassiopeia: 18 April 2012 - 08:54 AM