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.
If you have covered all the information you think you want to be able to manipulate, your current table structure seems good. Besides the table names and fields names, have you thought about each field's properties.
For instance, how many characters long, format, input mask (if needed), etcetera.
I have had the same concerns as you in my early projects. I consulted an expert whose response turns out to be very true. At the first you do the best you can with the design and purpose you have in mind now. Think it out and put it on paper. Now you are going to start to design forms and reports to access and manipulate your table information and as you do that your original design concept may change a bit, even to the point of altering a table design or table relationships slightly. This is all normal and to be expected. It is typical of the creative process.