[KLUG Programming] questions on picking data types

[bill]

On Wed, 2004-07-14 at 12:17, Rusty Yonkers wrote:
> 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.

> 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?  

Too vague a question to answer.  For a binary choice (where there are
only two exclusive choices) it might be better to use radio buttons
instead of a drop down list.  Radio buttons require one click, drop-down
lists require two).

For PHP:

// drop down list
echo "<select name=\"gender\" count=\"1\">
<option value=\"\">Please select gender</option>
<option value=\"m\">Male</option>
<option value=\"f\">Female</option>
</select>\n";

// radio buttons
echo "Please select gender:<br />
<input type=\"radio\" name=\"gender\" value=\"m\"> Male<br />
<input type=\"radio\" name=\"gender\" value=\"f\"> Female<br />\n";

Note that radio buttons must have the same name to be mutually
exclusive.

> Is that dependant on the programming environment that
> I am using (i.e. RealBASIC or PHP or VisualBASIC)?  
> 
> For doing the Social security number, I am thinking of creating three
> fields one for each of the three parts of the number so that I can
> have three fields on the screen and make it easier to make sure
> people enter it in consistantly.  

Screening data entry is an extremely good idea.

> 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.  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).

kind regards,

bill