1

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

1

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
  • 1
    Hey thanks man really nice of u to reply. Though i solved the problem be maintaining separate unlinked tables and the C++ code handle the entry and deletion in the table. Thanks again. – compiler Feb 20 '15 at 13:57
0

You will likely need at least two tables it sounds like. I don't fully understand what data you are wanting to capture, but probably need a table that has all the "products" and another table that has all the "transactions".

  • i can explain more if you want. – compiler Feb 14 '15 at 2:13
  • Up to you :) If you want further assistance, I can help more with more details. You need to come up with the most normalized version of your data. An easy way to think of relational dbs is like with a video store. You have a table of the actors, a table of the movies, a table of the movies actors which links those two. Then you have a table of customers who rent the movies. You have a table of Rentals summary which is the dates the rentals occurred tied to the customer. Then finally a rentals details table that links that summary to all the movies the customers rented on that day. – Chris Woods Feb 14 '15 at 2:50
  • 1
    What we need from you is an actual list of all the categories of data you want to capture. I don't mean the actual products, but just the types of data you want to capture. So you obviously have products, you have customers, what else do you want to capture? – Chris Woods Feb 14 '15 at 2:51
  • Hey thanks for reply, the problem is there are multiple instances of issue and receive. Also one medicine or item can have multiple batch number that where i get confused. – compiler Feb 14 '15 at 4:03
  • 1
    I am not sure what you mean by "multiple instances of issue and receive". And when you said "one medicine or item can have multiple batch number", do you mean one specific pill can have multiple batch numbers? – Chris Woods Feb 17 '15 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.