How to store units of measurement in a database

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".

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.

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.

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.

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

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.

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

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.

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…

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…