HI. For my practice I am starting working upon a project. A dummy project but scenario is real. This is a medicine retail shop project. I have a table
ProductInfo. In which there is product info and its price as well. Another Table called Store, which shows only ProductName and Its remaining stock.
Question:
For example the owner purchases stock for his own retail shop right. He purchases 20 boxes of metformin (a diabetic tablet) in which there are 10 tablets in
each pack and there are 5 packs so total 50 tablets. Right. My question is that How would I enter the record of this tablet into Product Table? Whether I
would insert the record in form of box i.e. 20 total or in form of packs i.e. 100 packs or in form of single tablet i.e. 1000 tablets?
- For example a customer comes and he asks for a full box of Metformin, I will sale a single box and Stock table will be updated and remaining stock of
Metformin box will be 19 right.
BUT IF the customer ask for only 10 tablets (i.e. 1 pack) , then how would I update the Stock table, because the stock in table is entered as boxwise not a
pack or tabletwise, so How would I subtract the number of tablets from 20 Packets?
OR in OTHER CASE For Example if a customer comes and he asks to purchase only 3 three tablets not a full box or, pack then how I would minus the 3 tablets
record from 20 Boxes?
I want to clear my concept of storing data into database that under what fashion I would insert record? that I could easily retrieve it as well?