Storing Fractions in MySQL

[David Edmonson]

I'm creating a database of pipe products. Their sizes are both whole
and fraction (ie 1 1/2" pipe). I plan to convert everything to decimal
for storage in the DB, but I wondered what is the be MySQL type to do
this?

Thanks - David

[Shawn Stratton]

Hi David,

The best solution to this is actually storing whole, numerator, denominator in separate tables.  The reason behind this is because computers are not great at working with fractions (they prefer decimals,) as such if you store 1/3 as a decimal (.33333333333) you will never get 1/3 back out of the table.  

Shawn Stratton
Zend Certified Engineer
Senior Systems Administrator - Digital Platforms - Discovery Communications, Inc

--
You received this message because you are subscribed to the Google Groups "AtlantaPHP Discussions and Job Postings" group.
To post to this group, send email to atlan...@googlegroups.com.
To unsubscribe from this group, send email to atlantaphp+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/atlantaphp?hl=en.

[Moses Ngone]

Actualyy If you dont need to make mathematical calculations with the sizes and the data is only for reference then the size is a text value and there is no need to convert it.

Store the sizes in their own columns or in its own table and have description columns with the actual text descriptions.

--
above the sky below the limit - strive

@mosesngone

http://mospired.com

[Rob Peck]

I would use two columns - one is a "friendly" text that you would present to a user ("1 1/3", "1 1/2", etc.). The other is a decimal/float that you can use to search/sort on/etc.

Rob Peck
r...@robpeck.com
http://www.robpeck.com

[R Matthew Songer]

Would this be one of those times when you would just have a size table with size_id for searching, sorting and size_name for display?

Matthew

[David Edmonson]

I think my answer is a combination of all of your answers:

Shawn: I used your method to come up with the number to store in the
DB. ie: 1 7/16" = 1.44; 2 1/4" = 2.25
Moses: I will need to do math and sorting, so text will not be ideal
Rob: I'll use decimal for the number I'm storing; undecided on making
a "friendly" version in the DB or doing that in the application
Matthew: Because I want to allow users to search the site by size, and
sort by size, and filter by size, then it probably makes the most
sense to make a table just for sizes;

There are "standard pipe sizes" which would have been easy to create
(1/2", 3/4", 1", etc.)however, each of the items (bulkhead fittings in
this case) have a specific hole size that the end user will have to
drill in order to use the fitting. These holes are not "standard pipe
sizes", but rather specific to each fitting.

So, I think it will make the most sense to have a table for standard
pipe sizes, then an item attribute that is "hole_size" and will be a
decimal.

I'm just thinking out loud here...

On Mar 14, 10:59 am, R Matthew Songer <rmson...@gmail.com> wrote:
> Would this be one of those times when you would just have a size table with
> size_id for searching, sorting and size_name for display?
>
> Matthew
>
>
>
>
>
>
>
> On Wed, Mar 14, 2012 at 10:16 AM, Moses Ngone <moh...@gmail.com> wrote:
> > Actualyy If you dont need to make mathematical calculations with the sizes
> > and the data is only for reference then the size is a text value and there
> > is no need to convert it.
> > Store the sizes in their own columns or in its own table and have
> > description columns with the actual text descriptions.
>

> > @mosesngone <http://twitter.com/mosesngone>

[R Matthew Songer]

Yep, I'd add the drill size as an attribute to each item that needed it. That seems to be not worthy of a separate table due to how varied it would be. Copper fittings are different from PVC which are different from iron pipe, what schedule they are changes it all again. I just don't know if a lookup table is worth it for that, but always question myself for going too far with normalization.

Matthew

[S B]

 

I have a suggestion which might strike you as a bit odd, but it would perfectly fit all possible scenarios.

 

Store the length as a ‘whole number multiple’ of the smallest measurable interval. For instance, if pipe diameters are measured in 1/16th of an inch, the smallest unit would be 1 u (just making that up that unit ‘u’ for convenience).

 

1 inch would be 16 u

2 1/4 inch would be 2 * 16 + 1/4 * 16 = 36 u

 

The point here is that you’re always storing the units as an integer, never as a fraction (which would be prone to rounding off errors). All your calculations would never give you problems, although when representing to the user, you’ll need to “convert” them into human-readable / more-intuitive forms.

 

Incidentally, this approach is followed in all financial transactions when “money” is stored in integer units of ‘pennies’ (technically, its stored in smaller units of those).

 

---

[KoNfUzEd]

That's also very much like millage rates and other tax information, where earlier systems didn't store the decimals well but a combination of symbols marked it differently. So 1240 mils %o = 1,240 thousandths percent or 1.240%, but it allowed the rate to be stored as a whole number.

**Michael

[Chase Peeler]

SB - I actually like that, but I think you are going to make things difficult by choosing fractional base units. For length, I'd definitely store everything in metric. This allows you to easily define a non fractional base unit.

Instead of defining visual representations for each item, you could also build a table of formats. Then you can define what format a certain item should use. You could also define multiple formats based on where it is being displayed.

You could then go pretty specific:
fractional_32_in => x y/32 in
fractional_32_in_quote => x y/32"

or, you could make it more generic, where you just have a fractional format type, and you define parameters for each item for when it uses a fractional format, denominator, units label, etc.

On Wed, Mar 14, 2012 at 12:23 PM, S B <silico...@gmail.com> wrote:

--
Chase Peeler
chase...@gmail.com