3 Replies - 5899 Views - Last Post: 18 April 2012 - 08:52 AM Rate Topic: -----

#1 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

inventory database design

Posted 26 March 2012 - 02:06 AM

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. :wacko:

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. :dead: 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


Is This A Good Question/Topic? 0
  • +

Replies To: inventory database design

#2 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: inventory database design

Posted 16 April 2012 - 11:05 AM

found the answer. :gunsmilie:

case closed
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 799
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: inventory database design

Posted 16 April 2012 - 07:02 PM

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.
Was This Post Helpful? 1
  • +
  • -

#4 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: inventory database design

Posted 18 April 2012 - 08:52 AM

hi, thanks for reply

View Poste_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 :whistling: )

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 :D )

and thank you for replying :)

-cassiopeia-

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1