i have designed tables for my store database. the purpose of my database is to make invoice for customer and input my buy invoice, calculate stock of my goods in the warehouse. here is the complete list :
* PRODUCT ID
* CUSTOMER ID
* INVOICE ID (AUTONUMBER)
BUY INVOICE NUMBER ( INVOICE NUMBER FROM SUPPLIER, CREATED BY SUPPLIER)
BUY INVOICE DATE
SELL INVOICE NUMBER (INVOICE NUMBER FROM MY STORE)
SELL INVOICE DATE
TRANSACTION DETAIL TABLE
* TRANSACTION ID (AUTONUMBER)
QUANTITY IN (BUY)
QUANTITY OUT (SELL)
THE RELATIONSHIPS ARE :
PRODUCT ID [PRODUCT TABLE] WITH PRODUCT ID [TRANSACTION DETAIL TABLE] (1 TO MANY)
INVOICE ID [TRANSACTION TABLE] WITH INVOICE ID [TRANSACTION DETAIL TABLE] (1 TO MANY)
SUPPLIER ID [SUPPLIER TABLE] WITH SUPPLIER ID [TRANSACTION TABLE] (1 TO MANY)
CUSTOMER ID [CUSTOMER TABLE] WITH CUSTOMER ID [TRANSACTION TABLE] (1 TO MANY)
is my design right? anyone help me to design the right way please.
the problem in my design is i can't check the stock of my goods before any transaction (buy or sell) of the goods. But after any transaction occur i can check it and it add the first stock of the good in my inventory. i want to know if it is possible to me to add the first stock of my goods and i can check it before any transaction occur.
thank you, sorry my english is bad.