how to create a look up table

Question

I have a spreadsheet that I have imported into MySQL.It has many rows of entries, including gambling odds i.e 2/1, or 7/2. Unfortunately the gambling odds are read as varchar by MySQL which makes it impossible to do calculations on them. It was suggested that I create a look-up table, where the gambling odds can be converted to there decimal values. This makes sense. OK so the big question is how do I go about this? Should I create a separate table that lists gambling odds and equates these to their decimal equivalents, if so, how would I make queries such as, find all the rows that have odds at 2/1 from table 1, and multiply this by £1. Any suggestions please?

Answer 1

I think a lookup table is too hard to maintain, since there are an infinite number of possible odds combinations.

Instead, I would strongly suggest that you create a view over your base table, that has the various fields that contain the odds:

create view v_table as
    select t.*,
           OddsTop*1.0/OddsBottom as OddsNumeric,
           OddsBottom*1.0/(OddsTop + OddsBottom) as OddsPvalue
    from (select t.*,
                 cast(left(t.odds, locate('/', t.odds, '/')-1) as int) as OddsTop, 
                 cast(substring(t.odds, locate('/', t.odds)+1, 100) as int) as OddsBottom, 
          from t
         ) t

You can easily calculate various types of information related to the odds. Here, I've shown how to get the top number, bottom number, odds as a floating point number, and the p-value equivalent.

Answer 2

This is actually quite an interesting question. My two-bits suggests that "7/2" isn't actually being used as a number, so could be interpreted as being a "code", in the same way that country codes are used instead of the whole country name. I might be inclined to go with the lookup table, using the Odds as the key, and the multiplication factors as columns in each row for use in math. You could also control how much precision you'd like to use, as well as have queries for high-odds and low-odds very easily.

Not necessarily saying I'm right here, just find this an interesting one.

Answer 3

As far as I know there is no datatype for it in MySQL. I would suggest not to create separate table as you suggest. If precision is not of utmost importance you can just store them as a decimal with a specific width and query with the decimal value. You can always convert it back to its fractional representation in the application layer. If simplicity matters you can store them as varchar. See here for a related question.