#1
  1. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,160
    Rep Power
    492

    Correct data types?


    Hello all
    I usually foul-up, sorry, post my questions in the PHP forum but today I have a MySQL Database question; probably a very easy one for you DB masters ... What are the correct data-types I should be using to store these values (this is a dump of a PHP array):-


    Array
    (
    [date] => 16/09/2012
    [time] => 15:30:46
    [anumber] => 1109907096175000
    [bnumber] => VOIP
    [origin] => 650
    [dialled]=> 440906001002
    [destination] => 323
    [duration] => 313
    [cost] => 0.1050
    )

    Thanks for any help
    Regards

    A Total Noob lol
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    [date] => 16/09/2012
    [time] => 15:30:46

    those could be condensed into a single column with a datetime data type

    [anumber] => 1109907096175000
    [bnumber] => VOIP
    [origin] => 650
    [dialled]=> 440906001002
    [destination] => 323
    [duration] => 313
    [cost] => 0.1050


    as for the rest what are you using now?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    6
    Rep Power
    0
    Code:
    [date] => 16/09/2012
        [time] => 15:30:46
    Are these actually just one timestamp? As already mentioned, you could use DATETIME. There is also the TIMESTAMP column type which allows for a default value of NOW() or CURRENT_TIMESTAMP(). If not related, then you can use the DATE and TIME data types instead.
    Code:
    [anumber] => 1109907096175000
        [bnumber] => VOIP
    These both look like they should be stored as text, i.e. VARCHAR(n) where n = the largest possible number of characters. This is especially true if the numbers might contain leading zeros.
    Code:
    [origin] => 650
    Is this a foreign key of some kind to a related table? If so, it should be the same data type as in the referred table, probably some kind of integer.
    Code:
    [dialled]=> 440906001002
    Again, VARCHAR() here;
    Code:
    [destination] => 323
    Same as [origin];
    Code:
    [duration] => 313
    Will there ever be a fractional value? If not, use INTEGER. Otherwise, either DECIMAL (or DOUBLE if rounding errors don't really matter);
    Code:
    [cost] => 0.1050
    Anytime you are dealing with monetary values, use the DECIMAL type.
  6. #4
  7. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,160
    Rep Power
    492
    Hi
    Sorry for not posting back sooner, thanks for the replies, most appreciated!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984

IMN logo majestic logo threadwatch logo seochat tools logo