Solved

How to store units of measurement in a database

Posted on 2014-11-25
9
334 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 108

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 108

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now