[Solved] How to design a database for medical products [closed]

Question

You need to have two tables, one for storing information about medicines and other one for inventory.

Databases are collection of tables that are related to each other. We can have one big table with all the information about medicines in it but it will cause a lot of issues while adding or updating the existing data.

Using your model of single table (name, manufacturer, category, expirydate) have a lot of issues. If I have five Paracetamol with different expiry dates, the other three columns (name, manufacture, category) will have the same data, which will be waste of resources. So we can move all the common data into another table and keep the different information in the different table. This way we will have two tables medicine(medicine_id, name, manufacturer, category) and inventory (inventory_id, medicine_id, expiry_date). Now we will enter common information into medicine table only once and update the expiry date in inventory table for each individual item.

This process is called Database normalization. You can find a lot of resources related to that on web.

Accepted Answer

You need to have two tables, one for storing information about medicines and other one for inventory.

Databases are collection of tables that are related to each other. We can have one big table with all the information about medicines in it but it will cause a lot of issues while adding or updating the existing data.

Using your model of single table (name, manufacturer, category, expirydate) have a lot of issues. If I have five Paracetamol with different expiry dates, the other three columns (name, manufacture, category) will have the same data, which will be waste of resources. So we can move all the common data into another table and keep the different information in the different table. This way we will have two tables medicine(medicine_id, name, manufacturer, category) and inventory (inventory_id, medicine_id, expiry_date). Now we will enter common information into medicine table only once and update the expiry date in inventory table for each individual item.

This process is called Database normalization. You can find a lot of resources related to that on web.