#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    23
    Rep Power
    0

    Choosing the right data type


    I am just learning to work with databases so this is a very basic question.

    I am working with MySql. I do not need the most efficient possible data storage. I want a database that is easy to use.

    I am tempted to use varchar for everything, but the lazy man's solution seems like it might cause problems later.

    1) Is using varchar for everything really a bad thing?

    2) I have a currency variable (currency symbol + decimal). The type of currency can change. Should I use a Varchar field for the symbol+decimal? Should I strip off the symbol and store it in a varchar and the decimal in a decimal field?

    3) Will using varchar for dates and times cause problems latter?

    (I tried to answer this by searching on google but the articles were more technical and less philosophical)
  2. #2
  3. No Profile Picture
    whatUseek
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Northville, Michigan
    Posts
    60
    Rep Power
    12
    Pick your data types more carefully. varchar is fine for strings, but for dates, use DATETIME. For currency, use DECIMAL with enough significant digits to satisfy all currency types used. If you use varying currencies in your live data set, then create a new column called currency_type and make that a varchar (since you like that column type so), or, be more clever, and try an ENUM (look it up in the MySQL docs if you don't know what it is).

    You see, varchar may look like a swell means to store all types of variables; however, when you are doing sorts in queries, that's when you'll pay the piper. There are just a plethora of column types -- explore the space. We need more cowbell.
  4. #3
  5. Mobbing Gangster
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Sep 2001
    Location
    "Best City" 2002 and 2003- Melbourne, Australia
    Posts
    4,912
    Rep Power
    32
    don't use enum too much as this is non-standard and requires a fair bit of hacking when you want to port to other dbs.
    And you know I mean that.
  6. #4
  7. No Profile Picture
    whatUseek
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Northville, Michigan
    Posts
    60
    Rep Power
    12
    Okay -- don't use ENUM, use VARCHAR. Heheh. Actually, if you do port to another DBMS, and you have ENUM (sounds like a disease now, eh?) see a doctor immediately. Actually, you can alter the column to be a <sigh> VARCHAR without losing any data.

IMN logo majestic logo threadwatch logo seochat tools logo