OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not.
So, my Dad works in a pharmacy and all the work there is on paper and is done manually. He asked me if I can develop an app which He can use. I'm stuck at the part where I gotto decide how to design the table for the system. My app is built on
QT/C++ and I'm using
Sqlite with it.
Here's the database problem, There are 6 different types of items coming the store, Eg. tables & capsules, injections, ointments, etc. Now for each item in each of these category, we have to maintain the following entries unique key, received date, received amount, issues date, issued amount, expiry date and batch number. Now this store is resupplied with items almost every week, and issues items to OPD frequently, also each item can receive different batch numbers which in turn has different expiry date. I want to design a table diagram which satisfies the problem.
~Thanks in advance.
Lets say we have a medicine
CROCINE (which comes under the category
tables & capsules) coming in in week 1. The batch number of the received medicine is
abc-123 and its expiry date it dd-mm-yyyy, and we received a quantity of 5000 units. Later in the week 1000 units are issued to the OPD. Further in the week another 2000 units are issued to the OPD. So the updated balance of CROCINE in the store is, 2000 units. Now, in the next week, ie. week 2, 2000 units of
CROCINE is received with batch number
def-456 with a different expiry date. Also the balance is updated. Like this it goes on and on. :p