Solved

How to store units of measurement in a database

Posted on 2014-11-25
9
406 Views
Last Modified: 2016-02-10
I've chosen to store all my companies products in MongoDB because we have a wide array of different items, all with different specifications.  So far, MongoDB has worked out great for our needs because we are able to completely customize each document to fit the exact specifications for our products.

With most of our items having measurements held within the specs, I'm looking to store the value of a the measurement in one field and the unit of measure in another.  The problem comes in when I have something that is 9' 9" or 30" X 20" X 25".

This is what I have currently, and remember this is in MongoDB.

{
     "category" : "547295b360557a18b87c4f2f",
     "category_id_old" : "2",
     "category_grp" : "H0004",
     "old_item_id" : "6",
     "model_number" : "Brick Basket",
     "capacity" : "400 lbs.",
     "weight" : "60 lbs.",
     "size" : "30\"x20\"x12\""
}

{
     "category" : "547295b760557a18b87c4f83",
     "category_id_old" : "75",
     "category_grp" : "H00030",
     "old_item_id" : "1727",
     "model_number" : "Eye-to-Eye",
     "capacity" : "5.6",
     "wire_rope_size" : "19\' 1\"",
     "size" : " 3/4\""
}

Open in new window


Here is what I would like to do:

{
     "category" : "547295b360557a18b87c4f2f",
     "category_id_old" : "2",
     "category_grp" : "H0004",
     "old_item_id" : "6",
     "model_number" : "Brick Basket",
     "capacity" : "400",
     "cap_uom" : "lbs", 
     "weight" : "60",
     "weight_uom" : "lbs", 
     "size" : "30\"x20\"x12\""
     "size_uom" : " ????? "
}

{
     "category" : "547295b760557a18b87c4f83",
     "category_id_old" : "75",
     "category_grp" : "H00030",
     "old_item_id" : "1727",
     "model_number" : "Eye-to-Eye",
     "capacity" : "5.6,
     "cap_uom" : "in."
     "wire_rope_size" : "19\' 1\"",
     "wire_rope_uom" : " ?????",
     "size" : " 3/4"
     "size_uom" : "in."
}

Open in new window


I've thought about possibly converting all the feet to inches, but then I'd need to have a flag on when to convert it back to feet, and when not to.  In the first product example, the size of 30" x 20" x 12" needs to stay in inches, but if I were to convert the wire_rope_size to inches, that would need to be converted back to 19' 1" when I display it on quotes for the customers.  

Thank you for any guidance you could provide.
0
Comment
Question by:t3chguy
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 16

Expert Comment

by:Brian Pringle
ID: 40465143
Ideally, you should not do this.  You should create new fields for width, height, and length if you need those additional fields.  If you really want to do this, then convert that field to a text-only field and save the whole thing as a string.
0
 
LVL 1

Author Comment

by:t3chguy
ID: 40465241
That would require a lot of work when displaying those figures on a web page, wouldn't it? How would I be able to dynamically be able to tell tell that width height length would all be combined...or even a depth if an item had a depth.

Also, what about the instance where a measurement is 9' 3"
0
 
LVL 16

Expert Comment

by:Brian Pringle
ID: 40465361
It would be a little bit more work to setup with the three values, but that is the preferred method.  If you make them strings, then it would be easier, but you could not use them for calculations.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 15

Expert Comment

by:ChloesDad
ID: 40465512
If you wanted to store a value as 9' 1" you could store it as 9.083 (i.e. 9 + 1/12) and then convert it back to feet and inches on the relevant page.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40465550
I agree to others - you should store single dimensions in three fields plus UoM fields. Additional option could be e.g. diameter for round things and already mentioned depth.

The presentation is another question. You may create a function which will combine above three fields and creates the output text. It should properly return any dimensions in words independently on number of known dimensions, i.e. boxes should have all three dimensions on output, one dimension is OK for rods etc. etc. This function can have additional parameter saying what system to use (metric/american) etc. In such case you will need additional table to store UoM conversions and maybe additional function to convert centimeters into feet/inches and back.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40465916
This is a great question, and it gets into the idea of a "Facade" in design patterns.  Let's consider the different nature of different products.  Here are some products worthy of consideration:

1. A box, 6" wide by 12" long by 12" deep
2. A rope, 3/4" in diameter by 1,500 feet long
3. A bucket, 18 quarts
4. A spring, 4" in diameter, 6" compressed, 8" extended
5. A weight, 2lbs
6. A basket, 24" x 24" x 24" rated for 417lbs

Now consider that each of these products could have variants.  A box, for example, might be as big as a freight container, 10' x 10' x 20' and a rope could be almost any arbitrary diameter and length (as well as different materials, but that's another topic).  The point of the facade design is that each product can be related to a named facade that encapsulates the dimensional values.  As long as the product can be described in terms of a particular facade name, its dimensions can be expressed in the facade.  All weights, for example, can be expressed in terms of ounces or pounds (or kg, etc), and all buckets can be expressed in terms of quarts.  These are examples, YMMV.

The facade object would contain data elements for any and all of the "dimensionally descriptive" data - length, diameter, weight, capacity, rating, etc., and each of these elements would be independent of the others in the object data model.  A method of the object would return a "clear text" description of the dimensional data that would be appropriate for the product.  There might be no obvious value to returning the weight of a rope, when most people would be interested in length, however the weight should be optionally specifiable for the rope, and if specified the facade object should be able to return it.

In a really well built facade, you would be able to insert new products and simultaneously insert new dimensions that describe the product; these dimensions would be fit into the facade seamlessly.  That may be a bit of a stretch, but it seems as if it can be doable.

Does this design make sense for your needs?  If not, please post back and let's discuss. ~Ray
0
 
LVL 1

Author Comment

by:t3chguy
ID: 40466705
It seems as if it would make sense, but I'm having a problem following, are you suggesting that if each of my items are in categories to store the data like this?

Category 1 for example would contain
description
manufacturer
model_number
capacity
capacity_uom
hol
hol_uom
weight_uom
weight_uom
min_head_room
min_head_room_uom
cfm_required

Category 2 would contain
description
capacity
capacity_uom
cfm
npt_inlet_outlet
npt_inlet_outlet_uom
psi
hose_diameter
hose_diameter_uom
hose_length
hose_length_uom

Because I've decided to store my products in a NOSQL Database (MongoDB), each category could have it own and unique set of specifications.
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40467940
I'm thinking more along the lines of something like this, with apologies if it's too relational in design.

id: auto_increment key
item_name: box
dimensional_description_id: (a box facade key)

Then in the box facade table...
id: auto_increment key
width:
length:
depth:

The box facade table could carry a great deal more information, including normalizations of the width, length and depth (perhaps into metric units), shipping weights, etc.  Ideally it would be responsible for (at least) carrying the appropriate dimensional data for web display.

There would be a rope facade table for ropes, a bucket facade table for buckets, etc.
0
 
LVL 1

Author Closing Comment

by:t3chguy
ID: 40636706
We ended up using your example in a relational database, and the headache was cured!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question