First of all, I would advise you to look for an Open-Source, off-the-shelf solution before you dive into writing your own. There are lots of free systems out there and there is no need, in my opinion, to re-invent the wheel.
However, if you want to do this I hope this helps...
Typically, in this type of a solution, you would want to break you data into the smallest blocks possible and create tables that would represent encapsulated (or independent) data. Then, use transaction tables to map the data in the manner you need to see it.
For example, the vendor table would contain only information about the vendor account. A VendorID which will be used everywhere else, the vendor name, address, payment terms, etc.
A second table would be your item master and would contain only information about the items the vendors provide. Again, an ItemID that would be used everywhere along with an item name, description, sell price, etc... This table would NOT have payment info, inventory, or anything else. Records should be generic and speak to the properties of a single item.
A transaction table would then be setup which would link the two. It would have a TransactionID along with an entry for a purchase ID and ONE ItemID. It should also contain the quantity and the price for that specific item.
You would also need a purchase table where the purchase ID would come from. It would also have information such as the order date/time, the vendor, who placed the order, etc.
When an order is placed or filled by the vendor, you would generate a purchase ID then add a row for each ItemID in that purchase into the transaction table setting the purchase ID to the proper value. At the end of this, if you received 20 items from the vendor, you would have 1 row in the purchase table that relates back to the vendor and 20 rows in the transaction table which link the items to the purchase (and by extension, the vendor). By querying these tables, (using some JOIN's) you can see all orders for a specific vendor, all orders for a specific item, or the total dollar spent with a specific vendor or item.
Another table would be only for payments and would contain a payment ID, vendor ID, and a dollar value. If you wanted to track it back to transactions, you would also include the purchaseID so you can reconcile the payments against the orders. A query that totals the transactions and subjects the sum of the payments for a given vendor will show you your outstanding balance.
The trick is to make sure that you are wrapping all the logical parts into their own tables then linking with the ID numbers. You want to avoid a situation where you start repeating data in the system or have to go back and edit records to keep them up to date. In your second table up above you included a column for payment status. This is not wise because you would have to continually edit and update that record. If you got into a situation where the system needed to scale and multiple people could be updating that, things can start to break real fast. It is better to always insert the data as independent entities then you have a record of everything and some simple SUM queries of LINQ statements can generate the results you need in real time.
You may choose, at some point, to keep running totals but put them in a different table and make sure you are calculating the totals from a stored procedure triggered or called based on some criteria. This would help with performance on a massive database but you are moving into a batch-type accounting system and you would be better advised to buy a real system at that point.
Submit an article or tip