Solved

How to store units of measurement in a database

Posted on 2014-11-25
9
493 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

697 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