March 3rd, 2003, 10:15 PM
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)
March 3rd, 2003, 11:17 PM
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.
March 3rd, 2003, 11:29 PM
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.
March 3rd, 2003, 11:34 PM
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.