Does this Database Table look Good to you?

Lord_Brar

Hey Guys -- I am finally getting my hands dirty with PHP / MySQL :evilgrin:

Here's a table I created for one of the first scripts I plan to put in production use. Was wondering if you guys could take a look and tell me if it is good or bad and what am I missing in it?


CREATE TABLE userdata (

firstname VARCHAR(64) NOT NULL default '',
lastname VARCHAR(64) NOT NULL default '',

dob SMALLINT NOT NULL,
mob SMALLINT NOT NULL,
yob SMALLINT NOT NULL,

email VARCHAR(128) NOT NULL,

active ENUM('0','1'),

gender ENUM('m','f'),
regdate DATETIME NOT NULL default '0000-00-00 00:00:00',

ipaddress VARCHAR(15) NOT NULL default '',

refby VARCHAR(32) NOT NULL default '',
campaign VARCHAR(32) NOT NULL default '',
keywords VARCHAR(32) NOT NULL default '',

id INT AUTO_INCREMENT PRIMARY

);

Ta!

NogDog

I would replace the 3 separate dob/mob/yob columns into one DATE type column.

Not knowing the purpose of the "campaign" column, it might be just fine as is, but if there can be any sort of one-to-many relationships, then it may need to be moved to a separate table(s).

Likewise, "keywords" could be its own table, with a keyword column and a user_id column, allowing you to create a many-to-one relationship between keywords and users.

zoooj

i agree with NogDog on the three points about the DoB, campaign and keywords columns. In addition, I'd set a limit to the first name/last name columns to 16/32 characters and also a 64 character limit to the email address column.

You've also got a refby column. This could well be another table too as you might want to keep a table availabale for referrers and one available for users. Someone may be both so this would keep them separate. You could do a quick look up on the referrer's table before entering them into the user's table if you wanted to flag the user as a referrer in the users table.

NogDog

zoooj;10883670 wrote:
...a 64 character limit to the email address column....

While it would admittedly be rare to find an email address that long, it is not prohibited by the applicable RFC's. The local part (the part before the "@") can be up to 64 characters itself, and the domain part can be up to 255 characters. Personally, I've never seen any email address anywhere near that long, but I usually set my DB email fields to be varchar(255) to ensure that it is highly unlikely I'll ever reject/truncate a valid email.

Ashley_Sheridan

I've seen one email address which comes close once, and that was the one you have to reply to when signing up to the php.net mailing list. I don't think it's quite 64 characters, but it's darn close!

Lord_Brar

Hey thanks a lot for all the help guys. BTW if I don't have the exact time then how do I convert year, month and date in single fleld? Can't use mktime() or can I?

NogDog

Lord Brar;10884060 wrote:
Hey thanks a lot for all the help guys. BTW if I don't have the exact time then how do I convert year, month and date in single fleld? Can't use mktime() or can I?

You can use 0 for the time fields:

$unixTimeSeconds = mktime(0, 0, 0, (int)$_POST['month'], (int)$_POST['day'], (int)$_POST['year']);

However, if this for inserting into the database and it's a DATETIME or DATE field, you could just do:

$dateString = sprintf("%04d-%02d-%02d", $_POST['year'], $_POST['month'], $_POST['day']);