hi...I need some help from you guyz. I'm still begginners on database design. I was ask to make an inventory system database. what are the tables that I need to build?.
So far, these are my tables that i make.but i think this is wrong.and i am confuse on this and what are the tables that i needed in the inventory system.I hope you can help me and teach me how to design database.Thank you in advance...
TABLE NAME: STOCKS
Fieldname Data Type Size DESCRIPTION
TransactionID Text 10 Transaction Identification Number
Date Date/Time N/A Date of Transaction
ProductName Text 20 Name of the product
ProductID Text 20 Product Identification Number
QuantityOnHand Number Long Integer Actual Quantity of Stocks
Cost Number Long Integer Price Of Buying from the Supplier
Primary Key:TransactionID
Foreign key:N/A
TABLE NAME: INVENTORY
Fieldname Data Type Size DESCRIPTION
ProductID Text 20 Product Identification Number
ProductName Text 20 Name of the Product
QuantityOnHand Number Long Integer Actual Quantity of Stocks
UnitPrice Number Long Integer Price of Selling the Products
Date Date/Time N/A Date of Transaction
Primary Key:ProductID
Foreign key:N/A
TABLE NAME: SALES
Fieldname Data Type Size DESCRIPTION
SalesInvoice Text 10 Sales Invoice Number
Date Date/Time N/A Date of the Sales Invoice
ProductID Text 20 Product Identification Number
UnitPrice Number Long Integer Price of Selling the Products
TotalSales Currency or General Number N/A Total Sales of the Products
Primary Key: SalesInvoice
Foreign key:N/A
TABLE NAME: QUANTITY UPDATE
Fieldname Data Type Size DESCRIPTION
ID Text 10 Product Identification Number
ProductID Text 20 Product Identification Number
QuantityUpdate Number Long Integer Update those are incoming stocks and remaining stocks
Primary Key:ID
Foreign key:N/A