I've got a software in PHP and postgres which i use for invoicing. I'm managing a stock management system which i produced. i'm trying to produce a stock movement page where I can tell whenever a part arrived, where it had been released so when as well as if this was credited (whether it was). I'm running 5 tables for that stock. My primary the first is part2vendor, parts, expenses, wo_parts and int_part_problem. After I receive stock, it is going in to the table part2vendor (onhand will get elevated by number received). The cost table will get the particulars from the part number, the supplier invoice and also the date received. wo_parts stores the part number released to some workorder. int_part_problem happens when I perform a stock adjustment or make use of a part internally. I'm searching to produce a PHP table that will list (in date order) the 'paper trail' of the part. I'm able to inform you table names and posts if needed. Thanks.
Seems like you'll need a simple history table? Posts
part_history id part_id date_modified (timestamp) action ( or maybe action_id if you have an actions table) vendor_id
And when you are getting a brand new part, and combine it with the parts2vendor table ( i believe u stated) you would employ the placed part ID (or unique part id ) to include an archive rto history
INSERT (id, part_id, action, vendor_id) 46565, 5757575, "Purchased", 757575
The date is going to be placed like a timestamp by postgres
Compared to any kind yuou can grab history depending around the uniquer id
sort by date_modified DESC.