Create Table - Mysql Code - Help

[alex1985 0]

I need your feedback about setting the database issues. Please, review them and correct some entries in the code if they got some mistakes.This is the code itself:

CREATE TABLE `news` (   `id` int(250) NOT NULL auto_increment,   `title` varchar(255) NOT NULL default '',   `text` text NOT NULL,   `author` varchar(255) NOT NULL default '',   `valid` varchar(255) NOT NULL default '',   `date` varchar(255) NOT NULL default '',   PRIMARY KEY  (`id`) ) ENGINE=MyISAM ;

[Erdemir 0]

CREATE TABLE `news` ( 
 `id` int(250) NOT NULL auto_increment, 
 `title` varchar(255) NOT NULL default '', 
 `text` text NOT NULL, 
 `author` varchar(255) NOT NULL default '', 
 `valid` varchar(255) NOT NULL default '', 
 `date` varchar(255) NOT NULL default '', 
 PRIMARY KEY  (`id`) 
) ENGINE=MyISAM ;

Length of title is 255. This is too small length. Maybe title can exceed 255. I know maximum length of varchar is 255. So we shouldn't use varchar. Instead we can use TEXT or LONGTEXT.

 

Selecting text for the coulomb `text` is a good choice

 

You shouldn't save date into strings so don't use varchar. Instead you can use int(16) and call it and save it by time() function. Analyze other scripts and you will see, using int for date and saving it by time() function is the best way to control the date.

//By the way time() function gives you the seconds from the 1/1/1970. How to use: <? echo( date("d.m.Y G:i:s ",$row['date']) ); ?>

But if you are already using time() function, again use int instead varchar, because if you are using varchar and if you want to sort by date the rows will be sorted as strings in the date coulomb but if you are using int when you sort by date the rows will be sorted as integer so sorted as date

 

I know it is not important but the length of id is to large, 250 means the length is 250. If I were you 15 would be enough.

 

My suggestion is:

CREATE TABLE `news` ( 
 `id` int(15) NOT NULL auto_increment, 
 `title` text NOT NULL default '', 
 `text` text NOT NULL, 
 `author` text NOT NULL default '', 
 `valid` text NOT NULL default '', 
 `date` int(16) NOT NULL default '', 
 PRIMARY KEY  (`id`) 
) ENGINE=MyISAM ;

Edited June 11, 2008 by Erdemir (see edit history)