Storing length or height in database in a way that won’t drive users crazy

1 Views

We’re trying to store something like a height or length in a database, such as feet inches. I cannot figure out the best way to approach this that stores clean data, but is also easy for the admin interface user to work with.

Example

5' 7"

I’m anticipating that I’ll need to sort by it later on as part of a search result function, but stored that way, it’s a string.

Read More

What seems most logical to me is to store the value as inches (numeric) in the database, but calculating that conversion to a single numeric value is far too much work for the people who will be using this content management system.

There’s also the concern that users may type it out any of several ways: 

5' 7"

5'7"

5' 7''

5’ 7”

5‘ 7“

Don’t ask why…we all know people do stupid things.

We’re using Advanced Custom Fields on top of WordPress, so I could build a “length” or “height” field type for use in this situation, which would handle any calculations necessary for the user, but I probably need a good plan of attack for the solution before attempting to build out the add-on.

Racking my brains trying to figure how to best archive this data. Any solutions are appreciated.

Post Views: 1

Related posts

Leave a Reply

5 comments

  1. Store it as inches. You can allow the user to enter it separately (one entry for feet, the other for inches), which allows for validations and removes the data entry issues, allows proper sorting easily (74″ is always more than 64″), and can easily be converted to feet/inches with division and modulo operations. (You can also do math on them much easier when stored as inches – it’s easier to figure out the difference between 60 and 74″ than it is the difference between 5’0″ and 6’2″.)

    As far as the users being unable to do the conversion, don’t require them to do it. You do it – convert feet/inches to inches on the way to being stored in the database, and convert it back to display it or allow editing.

    As I said, sorting is also much easier. Sorting numerics always keeps them in the proper order, while sorting character values can cause problems. For instance, numeric sorts always properly put 10 after 9, while character sorts would place 10 after 1.

  2. Lets break it in 2 pieces. The first is about how the users will input the data and the second how you will store it.

    1 – User Input
    You can provide a single field and allow the user to input date as you previewed (using different characters to identify feets/inches or different fields for feet and inches which will accept only numeric values.

    2 – Data Storage
    In case you provide only one field I suggest you to parse and validate the input and save it as a numeric value so you can easily order it but you’ll need to convert it from numeric to feet/inches every time you’re going to use these values.
    My other suggestion is to use 2 fields in your table so you can store feed in one and inches in the other. This will allow you to order the resultset and you’ll not waste processor time converting the values when you need to use them.

  3. Store in the most logical of formats – centimetres. Feet and inches?! Really!

    If your users need to see feet and inches inputs on the front end, convert to cm before storing in the database.

    So display on the front end using a function like this

    /**
 * Converts a height value given in cm to feet and inches
 *
 * @param int $cm
 * @return array
 */
public function convert_to_inches($cm) {
    $inches = round($cm * 0.393701);
    $result = [
        'ft' => intval($inches / 12),
        'in' => $inches % 12,
    ];

    return $result;
}

    And then convert to cm before storing in your database.

    /**
 * Converts a height value given in feet/inches to cm
 *
 * @param int $feet
 * @param int $inches
 * @return int
 */
public function convert_to_cm($feet, $inches) {
    $inches = ($feet * 12) + $inches;
    return (int) round($inches / 0.393701);
}

    The advantage to this is that you can create a front-end UI that offers both metric and imperial – because, you know, most of the world uses metric.

  4. You should normalize the value into a standard format for the database. I’d suggest centimeters or millimeters, since they lend themselves much better to arithmetic than imperial units, but that’s up to you.

    You should build a function which turns (somewhat) arbitrary user input into this standardized format. Say, a function which turns 5'7'', 5'7", 5' 7'' or 5' 7" into centimeters. The function should accept a reasonable number of reasonable formats, however many you feel like supporting, and reject other values it does not recognize.

    Then store this normalized value in your database and format it as feet/inches upon output as needed.

  5. The most logical thing to do would be use store the values as millimetres in the database, with one field for width and another for height.

    Then build a plug-in for the CMS that’ll convert input data (as a string) into the relevant floating point values in mms for database insertion… strangely I did something similar recently as a small JavaScript “program” to put a metric/imperial calculator on a website.

    Basically the JS takes user input strings and takes them apart with Regular Expressions to convert them to “unit blocks” which are then converted to millimetres.

    The RegExps look like:

    //regexps
  var reImperialLengths = /[0-9,./s]+s*(?:yd|yds|yard|yards|'|ft|foot|feet|"|in|ins|inch|inches)/i;
  var reImperialLengthsFormat = /^[^0-9]*(?:([0-9]+.?[0-9]*)s*(?:yd|yds|yard|yards))?s*(?:([0-9]+.?[0-9]*)s*(?:'|ft|foot|feet))?s*(?:([0-9]+)s*(?:([0-9]+)/([0-9]+))?s*(?:"|in|ins|inch|inches))?s*$/i;

  var reMetricLengths = /[0-9,.s]+s*(?:m|cm|mm)/i;
  var reMetricLengthsFormat = /^[^0-9]*(?:([0-9]+.?[0-9]*)s*(?:m|metres))?s*(?:([0-9]+.?[0-9]*)s*(?:cm|cms|centimetres?))?s*(?:([0-9]+)s*(?:mm|mms|millimetres))?s*$/i;

    This gives you a lot more flexibility with how the user can input the data and allows you to very easily do any calculations you may need since the program itself will be working with numbers.

    Just convert the numbers back to to how you want to display them on the front-end. You can prettify it there as well so that it ouputs 1' for every 304.8mms or whatever.

    Users can still stuff up the input strings but it gives them less ways to “get it wrong”.