SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    float or decimal ??

    I'm new to databases so would someone mind please explaining in simple (thats simple) terms the difference between Float and Decimal.

    FLOAT: Floating point numbers (single precision)
    DECIMAL:Floating point numbers represented as strings.

    Thanks.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Decimal is better to use for money columns (for example)
    Let's say you have a table "numbers" with columns
    f, float(10,2) and d, decimal(10,2)
    and then...
    insert into numbers set d=123456789*0.02, f=123456789*0.02
    What are the values of f and d?
    f = 2469135.75 (incorrect), d = 2469135.78 (correct)

    The decimal column takes up more space (one byte for each position), but it's char/int behaviour gives you the correct value
    The float column takes up less space (four bytes), but doesn't give you the correct value, since the value 0.02 used in the calculation isn't the real number 0.02 (dec), it's the binary approximation of 0.02 (dec) - something like 0.00110011... (bin), i.e. 1/8 + 1/16 + 1/128 + 1/256 ...

    That's my 246913578 cents worth

  3. #3
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankyou,nice and clear.
    Can you set decimal columns as null,if so do you have to state that its null or is it:

    cash decimal(10,2),

    AND......can you go to say 4,6,8 after the point,like so:

    cash decimal (10,6),

    Thanks

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the MySql docs:
    "DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
    ...the actual range for a given DECIMAL column may be constrained by the choice of M and D"

    e.g. you can't change a decimal(10,2) column to decimal(10,6), you must change it to a decimal(14,6), otherwise a value like 123456,12 will be changed to 99999,999999

    Yes, a decimal column can be null (setting default to 0 is a good idea)


    http://www.mysql.com/doc/en/Numeric_types.html

  5. #5
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need a degree in computer engineering to be able to understand the docs.

    I haven't even had a computer for that long.

    I know some of my questions may seem "dumb" but after reading for hours and still not being able to figure out what they are saying I find it becomes necessary to ask "dumb" questions.

    Thanks for your help.

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, at least you need to be a Ph.D. to understand how M and D works - it's still a mystery to me sometimes


    There are no stupid questions, just stupid people

  7. #7
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you saying that so long as I know what M and D are for then I dont need to understand anything else when using decimal ?

    See my problem is I want to learn how to build tables correctly,NOT just build tables,I can do that.I just dont see the point in having tables that end up giving you a corrupt DB.

    Normalization is important.But to me,the integrity of the database itself,not necessarily the means that you accomplish that integrity,is more important.Normalisation has little,if any value,if your columns aren't structured correctly.

    If I leave out NOT NULL in one column when building a table all the normalisation in the world aint going to help me out.

    Sure I can get around most things in my scripts.I can check to make sure values are in form fields etc,but how many oversights can I afford before information becomes corrupt.

    There are no stupid questions,just stupid people.
    So true

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, I guess I'm saying:
    Identify what the column should be used for.
    Exchange rates => use D=6
    Price of a product => D=2
    What's the largest possible value?
    My bank account => M=16
    Concert ticket prices => M=4 etc etc
    To enforce data integrity (inserted value not outside what the combination of M & D can handle), you must use the procedural approach, since there is no way to do it with a declaration in MySql (please correct me if I'm wrong)

    To NOT NULL or not NOT NULL?
    Default is NULL, and the rule is the same as always; is the value optional or not?
    I think always adding a default value is good. OK, MySql is very helpful and inserts 0 as default value for your decimal column if you specify NOT NULL, and you learn to rely on implicit declarations == bad.
    A decimal column should never be used as a key, so this isn't a normalisation question in that way. Of course, in some cases, when the column is optional (NULL), it's better to put it in a separate table, e.g. discount for a product when only a few products have this property.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •