TexasCHLforum.com

CREATE TABLE `registrations` (
`name` varchar(200) NOT NULL default '',
`phone` varchar(200) NOT NULL default '',
`etc` varchar(200) NOT NULL default '',
PRIMARY KEY (`rid`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

I understand this is just and example but for some specifics on how this start point could be imporved:


CREATE TABLE `registrations` (
`rid` int(10) unsigned not null auto_increment, // actually specify the id column being used as the primary key
`first_name` char(64) NOT NULL,//names are usually stored on column per name "part"
`last_name` char(64) NOT NULL ,
`registration_date` date not null,
`phone` char(13) NOT NULL default '', // if you're only expecting US registrants it's going to be a fixed width of 10 digits + 3 hyphens
PRIMARY KEY (`rid`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

the name columns changed from a varchar to a char column just b/c storage is cheap and i'm always a fan of fixed width rows when ever possible.

Also note the lack of defaults for the names. If this is a field you do not want to be blank and always want to be specified then do not set a default to an undesired value.

Note the choice of "date" instead of "datetime" for the registration_date. This is assuming you aren't interested in the specific time the record was creating, resulting in a smaller row.

If you were interested in the time, but planned on retrieving records just on the date part of registration you might consider then adding a registation_time column to store the time part. Then it's there but you only need to index what you use for the date column.

Edit: Oh yes, make the charset utf8 instead of latin1 to accommodate non english characters. Even if you're only expecting US residents you never know their families origin.

Well I was hoping to have something to contribute but it looks like most of what I was going to say was covered. One comment would be to avoid the older style mysql_ functions and use the mysqli library instead. You can take advantage of of parameter binding and prepared statements which 1) means you doing have to pass everything through an escaping function and 2) there are some performance benefits.

I'm always open to a PM for anything else or more specific questions.

Oh, also, once you get a release ready to go, please let me try to break it :)

Would be happy and able to do any code reads as well.

My day job is in a partial PHP/MySQL shop for a site that gets 125M pageviews/month :)