Idea for manking tables for a pharmacy

database-design

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.

Example:

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

Best Answer

Normalization is hard :/

Guys I had a go, but I doubt it's correct - please be gentle. For example we can SUM to get current stock, but the joins seem kind of unnecessary instead of using a figure stored somewhere. I would love to see the thought process of someone experienced designing this. Anyway:

enter image description here

Product
---
product ID
product name
category

1, aspirin, tablets
2, toxazon, injections
3, ibuprof, tablets

Batch
---
batch ID
product ID
expiry date

asp-001, 1, 2015-03-01
tox-001, 2, 2015-02-10
tox-002, 2, 2015-02-28

Transaction
---
batch ID
transaction date
amount

asp-001, 2015-01-01, 200
tox-001, 2015-01-30, 200
asp-001, 2015-02-01, -100
tox-002, 2015-02-07, 500
Related Question