Solved

How to store units of measurement in a database

Posted on 2014-11-25
9
553 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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 42

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 110

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 110

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

696 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