Which data type to use to store fractions?

mysql sql sqldatatypes

11,935

Solution 1

Two integers; Numerator and denominator. Insertion/storing will be pretty much the same speed for any data type which provides a reasonable representation of a fraction.

Solution 2

It depends on what you need the information for.

If it is stored for calculation and storage, calculate the result (0.92 instead of 92/100) and store that as decimal(1,5). But you can't easily calculate it backwards if you want to display it.

If it is stored to be displayed at a later time but never to be modified again (or at least not fast) you can store it as varchar but that will break sorting.

Or you could store the different elements (positives, negatives, totals, ... whatever) as decimal(5,0) and display / calculate it while using it.

And of course you can combine the above if you want to get the edge out of the computing time while selecting.

11,935

Author by

Muhammad Azhar

I am an IT professional with over 7 years of experience in various technologies. I worked on server side and client side. I had also worked on Windows and Linux, Apache, etc. I love to write code using PHP and Mysql. My recent web development projects were bank codes and education portals. One of my favorite project is Sbi ifsc code Sbiifsccode.co.in where i had developed an user friendly navigation with complete information of ifsc codes of all branches of SBI. The database is primarily provided by RBI and then regularly updated by me to maintain data freshness and accuracy. In banking sector it is common to transfer bank officials from one place to other. When the transfer happens, it is now our duty to update the new phone numbers of that branch officials. The web based application, provides various information fields for each branch like bank name, branch, address, state, district, ifsc code, micr code, branch code, customer care phone numbers, etc. One of my recent works on Bank ifsc code, micr, NEFT, RTGS, branch code and so on is MyIfscCodes.com. Find all your bank ifsc codes at above mentioned site, its quick and easy and most importantly accurate.

Updated on July 05, 2022

Comments

Muhammad Azhar almost 2 years

I have some values like 3/5, 90/100 etc.

I have to store these values in separate columns in my database.

I am confused as to which datatype to use for this purpose; which is faster for insert and select operations?
Muhammad Azhar almost 11 years

Thanks for your response, i have seo score 92/100 for a website, this was calculated from some php script i want to store it the same way and i don't make any changes and when user wants it, i have to display it same way like 92/100, so that, it is easy to understand.
Michael Durrant almost 11 years

Resist the temptation to use a String for number information. You, or the future you or your successor will regret it. Learned from many jobs.
Angelo Fuchs almost 11 years

@MichaelDurrant That depends. I would never use it alone, but if selection time is critical (and you're hunting nanos) it can be worth it. I don't think its appropriate in this scenario. But otoh I know very few about the OPs scenario.
Angelo Fuchs almost 11 years

without any further comments in what situations thats a good idea (and when not!) thats not a good idea.
Danny Beckett almost 11 years

@AngeloNeuschitzer Since no maths needs to be performed on the SEO score, the OP just wants it to be displayed back to the user in the same format. Storing it in VARCHAR will preserve it in exactly the same format as it was INSERTed in.