CODEWITHSUNDEEP
mysqlsqldatabase-designdatabase-schema
Liron Harel
Liron Harel

Reputation: 11247

Storing Ratio as number in MySQL database column

I want to store ratios in my database. An example of ratios are: 4:5, 6:7, etc. I want to make a query based on the ratios.

I thought about storing them as decimals (9,3) in MySQL in order for to query them as numbers and not texts. A ratio can be a long number like 1.77777777778 for 16:9.

What is the appropriate way to store it in MySQL database in order to query it later.

Upvotes: 1

Views: 1996

Answers (2)

ngrashia
ngrashia

Reputation: 9904

It is better to store it as 2 columns. Since 4:5 and 8:10 produces same results, you can get the exact values if stored in separate colums.

You can save as 2 INTEGER or NUMERIC values.

Your select query can be:

SELECT NUM1, NUM2, NUM1/NUM2 AS RATIO FROM MY_TABLE

Upvotes: 1

Barmar
Barmar

Reputation: 781004

Store them in two columns, numerator and denominator.

Either the code that inserts and updates the data should ensure that the ratios are reduced to lowest form, or when you're comparing you'll need to perform division, e.g.

SELECT blah
FROM table
WHERE numerator/denominator = 16/9

Upvotes: 2

Related Questions