Stock movement in PHP

Question

I have a software in PHP and postgres that I use for invoicing. I am running a stock management system that I created. i am trying to create a stock movement page where I can see when a part came in, where it was issued and when and also when it was credited (if it was). I am running 5 tables for the stock. My main one is part2vendor, parts, expenses, wo_parts and int_part_issue. When I receive stock, it goes into the table part2vendor (onhand gets increased by number received). The expense table gets the details of the part number, the supplier invoice and the date received. wo_parts stores the part number issued to a workorder. int_part_issue is when I do a stock adjustment or use a part internally. I am looking to create a PHP table that would list (in date order) the 'paper trail' of a part. I can let you know table names and columns if required. Thanks.

Answer 1

Sounds like you need a simple history table? Columns

part_history
  id
  part_id
  date_modified (timestamp)
  action ( or maybe action_id if you have an actions table)
  vendor_id

And when you get a new part, and add it to the parts2vendor table ( i think u said) you would use the inserted part ID (or unique part id ) to add a record rto history

INSERT
(id, part_id, action, vendor_id)
46565, 5757575, "Purchased", 757575

The date will be inserted as a timestamp by postgres

Than for any part yuou can grab history relying on the uniquer id

sort by date_modified DESC.