[thelist] mysql table construction - help selecting field types

Jackson Yee jyee at vt.edu
Sun Aug 31 21:31:23 CDT 2003

Dunstan Orchard wrote:
> I'm never sure, no matter how much I check the manual, what field types and
> sizes to assign when building my tables.

Just as a general note, Dunstan, you might want to use PHPMyAdmin or some 
other visual database organizer.  It makes constructing tables much easier 
when you can visualize what is happening.

You also might want to create the table one field at a time to see what works 
in the SQL and what doesn't.

As far as what field types to assign to tables, that comes with experience and 
use.  I commonly use nothing more than INT, TEXT, and DATETIME types nowadays 
because of their simplicity and extendability, but Rudy can  give you the 
exact types and the reasons to use each one much better than I ever could. 8-)

> Below I've listed the field names and the approximate length
> and type of the data being entered. I've also listed the sql for building my
> table.


You've got the basic idea down, so here's a couple of quick suggestions:

* Make reg_id AUTO_INCREMENT.  It's much easier to work with a field which 
automatically updates itself.

* Instead of using a decimal type to store the money amount, store it as the 
lowest common divisor and do the output formatting in code. (e.g. $125.25 
becomes 12525 cents)  If you haven't read the paper on why floating point 
calculations should be avoided, do a google search for "floating point 
representation calcuation imprecision"

* Keeping the card number in a database is a *huge* liability if you're not 
prepared to take the necessary steps to fully secure it.  (Quite a few sites 
have been hacked into and have had credit card numbers taken out.) Unless you 
really need to, it's much less liable to outsource monetary tranactions to a 
bank or credit card dealer.  If you're going to keep it, MySQL actually has a 
MONTH and YEAR data type for you to use.

* Reduce the names as much as possible while still making sense.  For example, 
"online_email" is rather redundant, as if the person wasn't on-line, he or she 
wouldn't have e-mail in the first place.  "postal_*" falls into much the same 
category.  Saving yourself keystrokes is always a good thing.

Jackson Yee

More information about the thelist mailing list