Strategy-design of a database for medication

Question

I am working on a database where it contains 3 main tables.

The first table is a list of medications with auto increment IDs.

id   name 

1    paracetamol 500mg

...

The second table will detect how many paracetamol box we have with a foreign key of id from the first table:

q_id   id   quantity

12     1    5

The third table will indicate each box how much pills does it contains and the barcode of each box:

p_id q_id nbr_pills_per_box barcode

100  12   30                32445332223

101  12   35                ...

102  12   30                ...

Does these 3 tables make a relational database or should I change the design and even the concept ?

Answer 1

I would suggest you merge the 1st 2 tables, since every box will contain a single type of medicine. This way querying your database would be faster as well.

Eg. You want to get the qty of medicines of a particular type, you will have to query by joining these 3 tables. Instead, add the medicine name column in the second table and for medicines with no stock you can get the data from a single table, instead of querying 2 tables.