# Storing Quantities

• ### Question

• hi all ,

i think that here in egypt , most companies when working with an application which has a "store" part ,

and the items stored have more than one shape of qty ,  like max_qty , mid_qty and min_qty ,

for example a pharmacy application , the user will store the midicine like this :

medicine "A"  ,   2 Cartons(max_qty) , 12 Packages(mid_qty) and 8 tablets(min_qty) ,

i know they convert all these quantity to the "Small qty" , and store the qty in the "Stock" table only in the small qty , so there is only one column called "Qty" in the stock table .

but i really do not know why ,

i worked on a pharmacy app , and now can see that it's simpler to create 3 col's "max , mid and min_qty" , and work with all of them without needing to convert all quantities when selling , purchasing or updating or in any transaction , am i right or is there something missing ?

Thursday, September 11, 2014 6:56 PM

### All replies

• What you are referring too is a conversion rate.

A product (medicine in your example) may come in 1 carton = 12 packages,  1 package = 10 tablets.  This is the reason why the convert them to the smallest quantity, and then convert them up if needed.  Some products may have different conversions, 1 carton = 10 packages or 1 package = 30 tablets.   You would need to account for all of the possibilities.

Thursday, September 11, 2014 7:03 PM

why not to store the medicine in 3 cols ( max , mid and min_qty) , without converting them ?

Thursday, September 11, 2014 7:11 PM
• This is how it similar data represented (AdventureWorks Product table):

```	[Size] [nvarchar](5) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[Weight] [decimal](8, 2) NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,```

In your case it should be

Qty

PackagingCode

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

• Edited by Thursday, September 11, 2014 8:31 PM
Thursday, September 11, 2014 8:30 PM
• This is how it similar data represented (AdventureWorks Product table):

```	[Size] [nvarchar](5) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[Weight] [decimal](8, 2) NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,```

In your case it should be

Qty

PackagingCode

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

so sorry , but did not understand anything from what you said .

it's one question ,

they store quantity in 1 col , why not to store them in  3 cols ? it's easier

Thursday, September 11, 2014 9:44 PM
• Yes, it is easier to store in 3 different columns, but if a new packaging type is introduced (say a bundle of 24 cartons), then you would have to create another column (which might be fine, as these fields are similar to calculated fields to ease the data manipulation). However, storing in a single column (qty) the lowest possible unit will account for any future packaging types (so, no column additions are necessary as new packaging types are introduced)

Satish Kartan http://www.sqlfood.com/

Friday, September 12, 2014 12:49 AM
• Good point , but is this the only point ?
Friday, September 12, 2014 3:23 AM
• Yes, you can go with this approach.... So say if  you sell 10 packages and 3 tables from the medicine "A"

you can simple update like

UPDATE tbl SET mid_qty=mid_qty-10,min_qty=min_qty-3

WHERE medicine ="A"

If it fit for the business requirements why not..

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Friday, September 12, 2014 4:20 AM
• Normally you store the qty in the lowest unit which is sold.  Then ramp up to others.  If you don't do it that way, if someone orders 100 pills, and you have 12 packages + 1 carton + 4 pills, how do you know if you have 100 pills on hand?

Friday, September 12, 2014 12:30 PM
•  if someone orders 100 pills, and you have 12 packages + 1 carton + 4 pills, how do you know if you have 100 pills on hand?

the same way if i store the qty in the small qty .

this is something arranged in the interface ,

before selling anything , i query the table which has the qty ,

if the qty in the table < qty i sell

then

give the user a message and

do not sell

end if ;

Saturday, September 13, 2014 1:56 PM
• Have the best of the both worlds. Have a qty column to store the lowest unit and have additional calculated fields to ease data manipulation. You might need to setup a DML trigger to update these calculated fields as the data changes.

Satish Kartan http://www.sqlfood.com/

Saturday, September 13, 2014 3:05 PM
• Your approach is better because right now there is no way to tell what is your hot item. Suppose you sell a lot of packages and the current system may ‘calculate’ it as 1 carton and you can not tell if u need to order more packages or cartons or strips etc.

Saturday, September 13, 2014 4:21 PM
• Have the best of the both worlds. Have a qty column to store the lowest unit and have additional calculated fields to ease data manipulation. You might need to setup a DML trigger to update these calculated fields as the data changes.

Satish Kartan http://www.sqlfood.com/

if i do so , then i have to update the 3cols , and the qty in min unit column .
Sunday, September 14, 2014 1:43 AM
• Have the best of the both worlds. Have a qty column to store the lowest unit and have additional calculated fields to ease data manipulation. You might need to setup a DML trigger to update these calculated fields as the data changes.

Satish Kartan http://www.sqlfood.com/

if i do so , then i have to update the 3cols , and the qty in min unit column .
Keep the lowest unit and whatever calculated fields you need (don't need to repeat the lowest unit field again with min qty)

Satish Kartan http://www.sqlfood.com/

Sunday, September 14, 2014 1:56 AM
• It's better keep or manage a single unit.

Conversation formula can be differ from product to product for that keep conversation formula( Pack size/Package)  with your product information .

Keep one quantity  field in your transactional table in smallest unit. When it is required say in report them make convert it through a global user defined function.

Sunday, September 14, 2014 11:19 AM
• I found a problem using 3 cols , and it is that i can buy 2 cartons , then i will have

2 cartons , 0 packages , 0 strips , so , if i try to sell 2 strips , i will not be able to do so .

this is the only problem i can see now .

if you see another one , please tell me .

Sunday, September 14, 2014 7:13 PM
• It is more related to the business logic , you cannot sell 2 strips because you SIMPLE do not have it in the WH...

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, September 15, 2014 5:50 AM