DBMS Client Requirements

Name​ - Amber Saxena

Client: ​Aman Shekhar, Vindhyawasini Medical Agency, Nabinagar

About the Client and Objective​: My client wants to expand his medical store business to
chain of medical stores within a city, and he wants me to build a management system for the
same which would manage all the branches of chain, his customers, his providers, affiliated
doctor, workers under the chain and all the transactions made to analyze his profit or loss
within given period of time.

Client’s Requirements​:

● Client wants to manage a chain of medical stores spread across a city. Each medical
store branch should have its own unique id, major location, complete address, contact
number and head worker of that branch who would manage all transactions related to
the respective store.

● Client wants to manage all the medicines sold by the agency at each medical store and
each branch will have its own stock of a particular medicine. Each medicine should
have its unique id, medicine name, maximum retail price per strip (smallest unit of
medicine to be sold), tablets present in one strip, expiration date, batch no., company
name.

● Client wants to manage all his providers from which he buys all medicines, each
medicine is bought from a atleast one provider and a provider can sell multiple
medicines. Each provider should have a unique id, provider name, contact number,
address.

● Client wants to manage all his customers by just storing in the database their unique id,
name and contact number.

● Client also wants to manage all the doctors which are affiliated to chain and all the
medicine prescribed by them are always available in any branch of the medical store
chain and for recommending our medical store they also take some commision per
recommendation. Each doctor should have a unique id, name of doctor, name of clinic,
contact number, major location of the clinic and the commision he takes for each
recommendation.
● Client wants to manage all the transactions of each branch with his providers, i.e, how
many medicines are bought from a provider and at what cost price which would later
help to find out total profit or loss between a time period. Each transaction should have
an id, id of branch which is making that transaction, id of provider from whom that
medicine is bought, medicine id and cost price per strip and date on which that
transaction was made. We cannot keep only medicine id for each transaction because
each branch can buy same medicine from two different provider and provider sell the
medicine at different cost price based on strips we are buying and maximum retail price
of medicine.

● Client wants to manage all the transactions of each branch with his customers, i.e., how
many medicines are bought by a customer along with the medicine id, bill id, number
of strips per medicine, date of transaction and doctor who prescribed him the medicine
and if doctor is one of the affiliated doctors then we also add check that how much
commision we have to pay the doctor for one bill.

● Client wants to manage stock of each branch of chain, i.e., how many strips of each
medicine are available in the individual branch.

● System should also manage from which provider we buy which all medicines.

● Manage data of all workers currently working for the agency. Data about worker should
include worker id, name of worker, contact number, valid id proof (like aadhar or PAN
number), permanent address, salary per month and joining date. Each worker should be
working under exactly one branch and one branch can have more than one worker.

● For individual branches, branch head should be able to see and manage all the data
about medicine in his branch. He should be able to see all the medicines which are out
of stock or soon going to be out of stock (less than 5 strips remaining) and the system
should ask the branch head for number of strips to order for each medicine and
generate a table specifying medicine id, seller id from whom to buy and number of strips
per medicine.

● Head of the agency should collect orders from each branch individually and should add
up all the orders for same provider and should order medicines in bulk from each
provider, i.e., if two or more branches are ordering same medicines from same provider
then number of strips of medicines for individual store should add up for giving bulk
order to provider.
● After receiving the given order to provider, stock for each branch should be updated
automatically and same is applicable while selling a medicine to a customer.
● For individual branches, branch head should be able to see all the medicines which
have expired so that they can be removed and stocks of medicines should be updated
accordingly.

● System should be able to generate a bill for a customer specifying the name of
customer, name of branch, name of medicine, quantity, price and total bill including
GST, and concerned stocks should be updated accordingly.

● Client also wants to manage substitutes of some medicines which can be given as an
alternative to a customer when a medicine is out of stock.

● Client wants to have a feature in the system with the help of which he can see all the
transactions, i.e., with provider and customer both and also the total profit or loss of
individual branch as well as of the whole chain between the given period of time. It has
to be more like a passbook kind of thing in which there will be all transactions sorted by
date and debit or credit accordingly and at the end total debit, total credit along with total
profit or loss during that time duration.

● There will three different kind of users for this system, first one is head of agency, only
who will have the privileges to add and remove providers, medicines and doctors
affiliated. Second one is head of each branch of chain only who will have the privileges
to update the stocks of medicines of concerned store like removing expired medicines,
ordering some medicines etc. Third type are normal workers in each branch who only
have privileges to generate bill for a customer.