Scenario: product inventory database, complete with Products, Locations, Inventory (counts), and Inventory_Transactions tables
Schema for tables in question: http://mbsoftwaresolutions.com/temp/partial_schema031808.htm I was going to have the Inventory table be populated automatic via triggers whenever a record is inserted into the Products table. Likewise similar triggers with the Locations table, so that every Product/Location combination was represented in the Inventory table. When a record was inserted in the Inventory_Transactions table, the i/u/d triggers there would call a generic procedure to recalculate the qty in stock for that particular iinventoryid (which can be represented as product/location unique pair too). 1) I'm thinking that I might prefer to use a multi-FK of product/location instead of iinventoryid in Inventory_Transactions, as it requires one less JOIN back to the Inventory table. 2) My approach to maintaining the QtyInStock values in Inventory (done via the triggers)....do you think this is the best approach? Any other comments appreciated...thanks! --Michael _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
