List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 27 1999 7:35am
Subject:Re: datatypes?
View as plain text  
On Di, 1999-07-27 18:01:52 +1200, W. J. Samplonius wrote:
> [...] I am not too sure what datatypes to use.

For detailed information on the different column types see manual
chapter "7.2 Column types".

> year ---- 4 numbers

There's a special column type YEAR exactly for this.
But if there's any chance that you later on also will need the month
or day, I'd recommend using the DATE type.

> make ------- 20 characters
> model -------- 20 char
> price ------ special characters and numbers $10,000.00

After all prices are only numbers, so just use FLOAT as column type
and store prices as pure numbers.
To get the right formating retrieve this data using the FORMAT()
function: FORMAT(price, 2), or with the prepended dollar sign:
CONCAT('$',FORMAT(price, 2)).

> picture_name ----- image filename 
[...]
> CREATE TABLE autos (
> year ??? (4),
> make VARCHAR (20),
> model VARCHAR (20),
> price ????? (10),
> pictur_name VARCHAR (25),
> );
> 
> is that correct?

My suggestion:
  CREATE TABLE autos (
    year YEAR
   ,make CHAR(20)
   ,model CHAR(20),
   ,price FLOAT,
   ,pictur_name CHAR(25)
   ,PRIMARY KEY (model,make,year)
  )

Notes:
- CHAR vs. VARCHAR: When using no VARCHAR column type then all fields
    have constant length and therefore MySQL also can use a fixed
    record length for storing such rows of data - this makes things
    quite fast on the expense of some wasted storage space.
    Performace is more important: use CHAR.
    As little space usage as possivel: use VARCHAR.
- PRIMARY KEY: I guess that you're going to use this table to look up
    prices and pictures for certain cars.  Then indexes on the
    identifying fields will speed things up very much.  Using a
    primary key both adds an index for this fields and ensures that
    you can't have duplicate entries in your table (several prices for
    the same model/make/year).

Regards,
  Martin

PS: Six years now since my NZ visit and still I start going into
    raptures if I only see .nz addresses ... ;-)
    Where to start if I'd like to work there for some years?
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
datatypes?W. J. Samplonius27 Jul
  • Re: datatypes?Martin Ramsch27 Jul
  • Re: datatypes?Paul DuBois27 Jul
RE: datatypes?Andreas Löding27 Jul