Reputation: 1763
Is for a small shop of Maximum 10 items.
As per current design, handling quantity in prod_master
table itself as shown below.
My confusion is, need to create another table to handle the query 'Whenever new stocks added'. May I know the standard way to design this ?
MariaDB [niffdb]> desc prod_master;
+--------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+----------------+
| prod_id | int(4) | NO | PRI | NULL | auto_increment |
| prod_desc | varchar(50) | NO | | NULL | |
| qty_in_stock | int(6) unsigned | NO | | 0 | |
+--------------+-----------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)
Planning to make another table stock_history
with fields prod_id
, date_added
and qty
in relation with prod_master
. But my doubt is , it is the standard way of doing ?
Upvotes: 0
Views: 126
Reputation: 31
For your purpose need two table , can name product_masters and product_transactions
In product_transactions you can add all the purchases with quantity and purchase date time. product master will always updated with latest stock ,average cost or latest purchased cost whatever you want. by this design you can quickly access the new stock see all the purchases made on products
Upvotes: 1
Reputation: 2619
Create a new table purchases with columns
id INT AUTO_INCREMENT PRIMARY KEY
purchase_date DATETIME
prod_id INT
quantity INT
Whenever you purchase something enter a new row in this table and update the quantity in the prod_master table as well.
Upvotes: 1