[KLUG Programming] questions on picking data types

[Adam Tauno WIlliams]

> > I am setting up the table structure for my new program.  I have some
> > items in some of the tables that I am struggling with what to use for
> > the data type.  For example I need gender.  I am thinking of using
> > ENUM so that I can have m or f listed as the choices.  I could also
> > use a binary type if that exists in mysql.  I would like to use a
> > binary type for questions that are y or n like if they have been in
> > for prior treatment, or if they are visually impaired.  
> Any of these will work.  Use what you're most comfortable with.  You'll
> be able to put whatever you want in the fields (y/n, 1/0, m/f, etc.), so
> use whatever type you want to put in there.

BLOB and TEXT type are both supported by MySQL and while not "standard"
(as in SQL92/99) they are in every other Db as well (or everyone I've
met).

MEDIUMINT, SET, and ENUM are MySQL specific oddities and if portability
of the schema or even interoperability with client side tools is worth
anything to you then these types should not be used.  Some ODBC/JDBC
tools go truly wonky on 'odd' types.

I don't know if MySQL supports CHECK constraints, but if it does then
that is a more palatable solution to limiting a field to a given set of
values

fieldname CHAR(1) CHECK (fieldname = 'Y' OR fieldname = 'N')

> > Am I assuming right also that with the ENUM data type that I will
> > have the list available within the GUI programming environment in a
> > dropdown list?  

I doubt this behaviour will be automatic, you app will need to acquire
the enumeration somehow - which often makes using a foreign keyed "code"
table worthwhile, even for trivial numbers of enumerates.

> > I was thinking that I would just
> > use smallint for each of the three fields.  Is it much better to make
> > the middle two digit number a tinyint since I would never have
> > anything higher than 99 in that field?
> It is theoretically better to define a field with the smallest possible
> scope to handle all possible data.  It makes the DB faster, uses less
> space, etc.  With MySQL, you'll probably never notice the difference, so
> the difference between smallint and tinyint is unmeasurable in practice,
> and so unnecessary in development.  
> In this case, just define the fields as a type of int in the size you
> want.  

Yep.  I usually just use INT since it is generic, and use some other
mechanism to constrain the value.  Be aware that in MANY cases something
like a smallint (which also exists in PostgreSQL, Informix, etc...) will
just get 'thunked up' to a 32 bit integer by the application environment
anyway.  PHP does this, and so do most others.  Reading a smallint just
creates an int (in RAM on the client), so very little (if anything) is
effectively gained.

> But in most cases of defining field size, I'd lean a little
> bigger than smaller because it's not worth the grief when you suddenly
> discover your data is bigger than your defined field size.  I'd rather
> sacrifice a little speed I can't notice than have a big whopping data
> entry failure stopping a user in his tracks (or even worse, truncating
> their data unexpectedly).

Yep.  Disk & RAM & CPU are cheap - best to 'err with a too generous data
model than a too thin one.