Fractions
It’s been a while since my last post. I’ve been busy on a few projects with upcoming deadlines. Harder still, I am working on both a Ruby on Rails and a PHP project at the same time and switching mindsets is challenging. (I can’t count the number of times I type self when I mean $this and vice versa.) It is like traveling between two foreign countries several times a day. I speak French to the German baker and German to the French butcher. (Or, most often, I am speechless in both cases!)
For the PHP project, I need to work with fractions of an inch in a few places. Not as decimals, but as fractions (1/5, 5/12, 3/64, etc.). Fractions are not an uncommon problem for developers. Since there seem to be very few discussions online about the subject, I thought it would be worthwhile to review some of the options. My example code will be in PHP since that’s the “country” I’m in right now, but the Ruby code is easy to extrapolate.
1. Store the fraction as a string
This is by far the easiest method: store the string in a VARCHAR field in the database. You simply consider the fraction to be text and treat it as text.
The main drawback to this approach is the same drawback you have anytime you store a number as a string—it ceases to be a number. You won’t be able to perform calculations, comparisons or sorting on it while it is in the database. For example, it becomes impossible to get all records with values between 1/8″ and 3/4″ sorted from from lowest to highest. Instead you would need to retrieve all records from the database, convert the value to a number and then perform the search and sorting. Not very efficient, especially if your database is large.
Still, the simplicity of this technique does a lot to recommend it. If you don’t need to search and sort, it is the best option. Maybe you are working with furniture and you just need to display the specs (height, width, depth), but user won’t be able to search or sort those specs, only display them once a product has been pulled up. The fraction-as-string method works great in this case because that is essentially all those specs are: text that needs to be displayed.
And it’s not like you can never convert fractions stored as strings if the need arises. Here’s some sample code on how to turn a fraction (string) into a decimal.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <?php function frac2dec( $string="", $precision=6 ) { // make sure it is a string and remove any commas $string = str_replace(',', '', (string)$string); // split whole number from fractional portion $string_array = explode(" ", $string); // empty $fraction means there was no whole number if(!empty($string_array[1])) { $whole = $string_array[0]; $fraction = $string_array[1]; } else { $whole = 0; $fraction = $string_array[0]; } // split fraction into numerator and denominator $string_array = explode("/", (string)$fraction); // empty $string_array[1] means there was no fraction // in other words: $string_array[0] is a whole number if(!empty($string_array[1])) { $fraction_result = (int)$string_array[0] / (int)$string_array[1]; } else { $fraction_result = (int)$string_array[0]; } // put it all together and return a rounded value $decimal = (int)$whole + $fraction_result; return round($decimal, $precision); } ?> |
2. Store the numerator and dominator in separate database fields
It clutters up your database a bit by requiring two fields to hold one value, but it makes rebuilding the fraction easier. In most cases you would either need a third database field to store the whole-number portion (the “3″ in 3 3/4) or you would need to write code to convert any whole number into a single “top-heavy” fraction (3 3/4 becomes 15/4) before saving it to the database.
Rebuilding the fraction to work with it is simple.
1 2 3 4 5 6 7 | <?php // As a decimal for mathematical operations $decimal = $numerator/$denominator; // As text for display $output = $numerator."/".$denominator; ?> |
Even better, if you are an SQL pro, you can write SELECT and ORDER BY statements that will perform calculations on those two fields. If you are an SQL pro, you will also able to take into account the processor work required to perform such calculations in each case. On large data sets, it could be considerable.
This is not a bad option, but I find it to be a bit inelegant. If you use a lot of fractions you would need to keep track of a lot of numerator and denominator fields. Personally, I also can’t get excited about writing complicated SQL calculations. And it would be an important detail to always remember that you have to get retrieve both fields!
3. Store the numerator and dominator in DECIMAL field
The idea is that you can put both the numerator and the denominator into a single database field if you put the numerator to the left of the decimal and the denominator to the right. So 3/4 becomes 3.4. It is an interesting and creative approach that many internet resources have recommended.
I fail to see any real benefit and strongly caution you against this technique.
Proponents might argue that it works a lot like the previous technique but takes up only one database field. True, the previous technique trade an extra field for the ability to search and sort as well as the easy retrieval of the values. This method takes back that field but doesn’t retain either of those benefits or gain any new ones.
The decimal-as-string method takes only one field too. Conversion back to an actual decimal isn’t much easier with the DECIMAL field than with a VARCHAR field. You still have to split the value and analyze it. Worse than a VARCHAR field, it requires pre-processing before putting data into the database—develop top-heavy fractions and then convert those to a “fake” decimal.
It also can present a big problem if you have a fraction like 21/100 that gets stored as 21.1 in a DECIMAL. Sure, you could write code that would add back in zeros, but how would you know if it is 21/100 or 21/1000? You don’t even have to look hard for examples that break down: is 1.2 equal to 1/2 or 1/20?
In my opinion the ultimate downside is that you end up with data in the database that is “lying” about itself. It’s “encrypted” but encrypted so as to be commonly mistaken for “unencrypted”. The field reads 3.4, but 3 2/5 is very different from 3/4. What if another developer accesses the same database without knowing about your conversion scheme? I always remind myself that such misunderstandings are how ships sink and satellites fall out of the sky.
4. Convert the number to a decimal equivalent
The advantage of storing the value as a decimal is obvious—SQL can easily search and sort on that field. It couldn’t be any easier to find all values between 2.5 and 3.875 and order them from lowest to highest.
Converting and storing fractions is easy too. You can use the frac2dec function shown above.
The problem with decimals is that they are not precisely equivalent to fractions. 1/3 is not exactly equal to .333333, no matter how many 3’s you add to it. In an application that requires a lot of precision that could be a problem in itself. But even for the most casual uses, it presents a problem when you try to convert a decimal back to a fraction.
Before we explore that further, let me present some code that will convert a decimal back to a fraction. It can either preserve any whole number or add it into the fraction (making it “top-heavy”). You will notice that it also takes reducible fractions like 25/100 and reduces them to 1/4 by determining the greatest common denominator between the numerator and denominator.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?php function dec2frac( $decimal=0, $top_heavy=false ) { // split the float on the decimal point into two strings list($whole,$mantissa) = explode(".", (string)$decimal); // Determine the "magnitude" of the decimal $den = pow(10, strlen($mantissa)); // "378" would be 1000 as in 378/1000 // if there is nothing right of the decimal, it's a whole number if($den == 1) { return $whole; } $whole = (int)$whole; $num = (int)$mantissa; // reduce the fraction using GCD theorem list($num, $den) = reduce_fraction($num, $den); // You can choose output as "5/4" (top heavy) or "1 1/4" if(!$top_heavy && $whole > 0) { return "{$whole} {$num}/{$den}"; } else { $num += ($whole * $den); return "{$num}/{$den}"; } } function reduce_fraction($num, $den) { $gcd = gcd($num, $den); $reduced_num = ($num / $gcd); $reduced_den = ($den / $gcd); return array($reduced_num, $reduced_den); } // Greatest Common Denominator function gcd($m, $n) { while ($n != 0) { $r=$m%$n; $m=$n; $n=$r; } return $m; } ?> |
This works great for any fraction where the denominator is 2, 4, 5, 8, 10, 16, 20, 25. Basically any denominator that is a power of 2, 5 or 10. That makes sense since we are reducing it from a decimal that starts with a denominator that is multiple of 10 (10, 100, 1000…). But try converting .333333 to a fraction with this function and you’ll get 333333/1000000. That’s not what we want if the 1/3 was the starting fraction.
There are some corrective techniques you can apply, some of them might be enough for your purposes, but there is no way to fully solve the problem. After all, how is a computer to know that .333333 should be 1/3 and not 333333/1000000? A computer deals in precision while you want a computer to fudge the numbers. You are telling it: “Even though you see 333333/1000000, that’s close enough to 1/3 that you should call it 1/3″. But at the same time, you probably would not want it to fudge .333332 and .333334 as 1/3!
Fudging the Decimal Conversion
How can you fudge the numbers? First, you might “suggest” to the conversion a denominator it should try to reduce below. For example, if the denominator of the reduced fraction over 100, attempt a few techniques to reduce it further.
The easiest way is to have a table of “special cases” where you look up values. That would be tedious and inflexible, but it would do the trick.
There is an old math trick that if a number repeats in a fraction, you can simply put that portion over the same number of 9’s to find the fraction. So .333333 would be 333333/999999 which is 1/3. So to fudge the numbers a little, you could try to subtract one from the denominator and see if that gives you a better reduction. That would fix 1/3, but not 2/3 which is .666667. It wouldn’t work because the numerator was rounded up when the decimal number was created. So let’s add a second pass that subtracts one from the numerator too.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php // this code would go right after the first call to reduce_fraction if($reduced_den > 100) { list($alt_num, $alt_den) = reduce_fraction($num, ($den - 1)); if($alt_den < ($den - 1) ) { $num = $alt_num; $den = $alt_den; } else { list($alt_num, $alt_den) = reduce_fraction(($num-1), ($den - 1)); if($alt_den < ($den - 1) ) { $num = $alt_num; $den = $alt_den; } else { $num = $reduced_num; $den = $reduced_den; } } } else { $num = $reduced_num; $den = $reduced_den; } ?> |
This cheat takes care of a lot of the special cases, but 1/6, 5/6, 6/7 and fractions with denominators larger than 10 are still a problem.
The math trick can be improved further by watching for sequences of numbers and putting the sequence over 9’s. For example .102102102 is equal to 102/999. That requires some more complicated code to look for patterns in the numbers. (I have not attempted it.) It would also need to allow for non-repeating digits followed by repeating digits. Using 1/6 as an example, .166667 has to become 0.1 + 66666/999990. (Notice the addition and the zero in the denominator.) .833333 becomes 0.8 + 33333/999990 which becomes 5/6.
The final problem is that even once you get an impressive bit of pattern-seeking, number-fudging code together, the number of digits may not be enough to reveal the pattern. That’s a lot of work for something that still might give you unreliable results!
My Solution: A Hybrid Approach
“Alright already. Just tell me what to use!”
Of course, the best solution will always be what works best for your particular situation. A developer displaying furniture sizes and a developer calculating scientific measurements have radically different needs. But for general use, I recommend the storing the fraction as a string and as a decimal. It is a hybrid approach that gives the best of both worlds.
As an example let’s say we are storing the height of a product. You would add two fields to your database. The first is “product_height”, a VARCHAR field, and you simply save the fraction as a string into it. Before creating or updating a record in the database, you also use the frac2dec function above to create a decimal that you store in a DECIMAL field called “product_height_decimal”. (You could also write code to only recalculate this value if “product_height” was changed.)
Yes, it takes up two database fields but both fields are clearly labeled and don’t require any extra-database processing for their data to have meaning. I think that’s a plus. We also don’t have to be an SQL guru to write code that will search and sort based on several fields, we can just search and sort based the decimal value stored in “product_height_decimal”. Another plus. To simply display the value, we can retrieve the string in “product_height” and display it without any processing or conversion inaccuracies. Another plus. If cases where we need mathematical precision we can use the string in “product_height” and take the time to extract the numerator and denominator.
There is one potential pitfall. If users are entering the fractions into a form field, you will need to make sure that you ensure that the fraction is legitimate. Strip out any spaces. Check it against a regular expression. That sort of thing. You might make it easier by providing a pulldown menu of common choices on the form.
One field for searching and sorting. One field for display and for accuracy. Not a lot of complicated code or intensive processing. I think it is elegant and easy to implement.
