CODEWITHSUNDEEP
mysqlmariadb
Arun
Arun

Reputation: 1763

Database table design for handle 'stock' details MySQL

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

Answers (2)

Suhail Pallimalil
Suhail Pallimalil

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

Thallius
Thallius

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

Related Questions