LineItem table is basically a joint table between Invoice and Inventory table. OK I am happy with that. I know how to link the 3 tables together.

What I would like to know is where does the data "price", "quantity", "subtotal", ""date of creation of invoice" "total" from the layout go? Do they go in the LineItem or Invoice Table and why?

Does the invoice table only have these fields-> Invoice_ID(PK), Customer_ID(FK) and LineItem_ID(FK)

Inventory Table

Item

Price (Current Prices)

LineItems Table

Price (lookup value, prices change over time)

Qty

Invoice Table

Subtotal

Total

InvoiceDate

The Invoice table has all fields that are required to describe the invoice, and the invoice alone (i.e. nothing about its relationship to the Inventory table). That would include InvoiceID, Date, CustomerID (or the actual customer data), and the invoice totals. The Invoice table does NOT have a foreign key for its LineItems.

The Inventory table has all fields that are required to describe a product, and the product alone (i.e. nothing about its relationship to the Invoice table).

The LineItems table describes the relationships between invoices and products. Basically, a line item record says: this invoice (InvoiceID) was used to purchase this many pieces (Quantity) of this product (InventoryID) at this price (PriceLookUp). The total price of this item is (ExtendedPrice = Quantity * PriceLookUp).

Thank you Mr Vodka and comment! It makes sense to me now.

I have a small question. I hope you guys could help me out here.

I have 3 fields in Inventory table: Stock_in_hand,Minimum_stock_level,Stock_available

Stock_in_hand = simply enter the quantity when u first create an inventory

Minimum_stock_level = is simply the minimum level of stock to be kept

Stock_available = Stock in Hand - LineItem::Quantity (does this sound correct)? It works fine and the deduction happens.

But when I go back to my inventory layout,I still see Stock_in_hand = same as before. Well this is bound to happend as "Stock_in_hand" is simply a number enter by a user.

Is there any better way of handling "Stock_in_hand", and "Stock_available"

.....................................

Also just one more question. When I am selling some product/inventory I want make sure "Quanity field" in the LineItem table checks to see if Quanity entered

Thank you for your kind help.

But when I go back to my inventory layout,I still see Stock_in_hand = same as before. Well this is bound to happend as "Stock_in_hand" is simply a number enter by a user.

Well that will give you what is in hand at that moment, but it will not update the qty. You need to have script steps update the Qty upon the completion of the invoice.

As for your other question, you can use a field validation by calculation.

Depends on how deep you want to go, but the simplest way is to have a Count and a CountDate. You'd relate your LineItems to your inventory by:

idInventory=idInventory

DateSold>CountDate

Then your TotalStock = Count - Sum(LineItems::Quantity)