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 TABLE
FIELD :
* PRODUCT ID
PRODUCT NAME
FIRST STOCK

CUSTOMER TABLE
FIELD :
* CUSTOMER ID
CUSTOMER NAME
etc....

SUPPLIER TABLE
FIELD :
*SUPPLIER ID
SUPPLIER NAME
etc.....

TRANSACTION TABLE
FIELD :
* INVOICE ID (AUTONUMBER)
SUPPLIER ID
BUY INVOICE NUMBER ( INVOICE NUMBER FROM SUPPLIER, CREATED BY SUPPLIER)
BUY INVOICE DATE
SELL INVOICE NUMBER (INVOICE NUMBER FROM MY STORE)
CUSTOMER ID
SELL INVOICE DATE

TRANSACTION DETAIL TABLE
FIELD :
* TRANSACTION ID (AUTONUMBER)
PRODUCT ID
PRODUCT NAME
QUANTITY IN (BUY)
BUY PRICE
QUANTITY OUT (SELL)
SELL PRICE

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.
__________________
hendra gunadi