SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    some mysql advice needed

    can anyone tell me if this looks ok? im doin a mysql with php site, the column types and default values im not too sure about and just wanted a 2nd opinion with someone knowin how to do this stuff hehe....

    also the date and time one, is there anyway to save it as dd-mm-yyyy hh:mm:ss instead of reserve (thats all it given in the mysql manual)?

    wasnt sure if those enum ones are right either, also i set everythin to not null.. such i hope again isnt bad design hehe.

    thanks for any advice

    Code:
    CREATE TABLE user (
     	id int(11) NOT NULL auto_increment,
     	username varchar(22) NOT NULL default '',
     	password varchar(32) NOT NULL default '',
     	email varchar(155) NOT NULL default '',
     	phone int(15) NOT NULL default '0',
     	address1 text(20) NOT NULL default '',
     	address2 text(20) NOT NULL default '',
     	address3 text(20) NOT NULL default '',
     	company text(15) NOT NULL default '',
     	date_joined date NOT NULL default '0000-00-00',
     	last_act datetime NOT NULL default '0000-00-00 00:00:00',
     	status enum('Awaiting','Rejected','Accepted') NOT NULL default 'Awaiting',
     	group enum('Normal','Admin','Owner') NOT NULL default 'Normal',
     	count int(10) NOT NULL,
     	PRIMARY KEY (id)
     ) TYPE=MyISAM;
     
     CREATE TABLE article (
     	id int(11) NOT NULL auto_increment,
     	title text(40) NOT NULL default '',
     	version text(8) NOT NULL default '',
     	description text NOT NULL,
     	category text(4) NOT NULL default '',
     	date_sub date NOT NULL default '0000-00-00',
     	status enum('New','Updated') NOT NULL,
     	username varchar(22) NOT NULL default '',
     	location text NOT NULL,
     	filename text NOT NULL,
     	extension text NOT NULL,
     	count int(10) NOT NULL,
     	PRIMARY KEY (id)
     ) TYPE=MyISAM;
     
     CREATE TABLE help (
     	id int(11) NOT NULL auto_increment,
     	question text NOT NULL,
     	answer text NOT NULL,
     	PRIMARY KEY (id)
     ) TYPE=MyISAM;

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    On only a quick glance

    address1 text(20) NOT NULL default '',
    address2 text(20) NOT NULL default '',
    address3 text(20) NOT NULL default '',

    Everyone has three address lines do they? And each one will fit into twenty characters? No way.
    Ditto company at 15 characters

    set the address lines (and company) to about 40 or 50 and make it varchar. And it might be safer NOT setting the 2nd and 3rd address to not null.
    You use text type a lot. Why?

  3. #3
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i changed it a little (got rid of the many defaults i had, not sure if that was a good move or not).

    what should I use then if I shouldnt be using text type?

    anymore advice from anyone?

    Code:
    CREATE TABLE member (
     	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	username varchar(22) NOT NULL,
     	password varchar(32) NOT NULL,
     	email varchar(155) NOT NULL,
     	phone varchar(15) NOT NULL,
     	street text(50) NOT NULL,
     	town text(50) NOT NULL,
     	county text(50) NOT NULL,
     	company varchar(40) NOT NULL,
     	date_joined date NOT NULL default '0000-00-00',
     	last_act datetime NOT NULL default '0000-00-00 00:00:00',
     	status enum('Awaiting','Rejected','Accepted') NOT NULL default 'Awaiting',
     	group enum('Normal','Admin','Owner') NOT NULL default 'Normal',
     	count int(10) NOT NULL
     ) TYPE=MyISAM;
     
     CREATE TABLE asset (
     	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	title text(50) NOT NULL,
     	version text(8) NOT NULL,
     	description text NOT NULL,
     	category text(4) NOT NULL,
     	date_sub date NOT NULL default '0000-00-00',
     	status enum('New','Updated') NOT NULL,
     	username varchar(22) NOT NULL,
     	location text NOT NULL,
     	filename text NOT NULL,
     	extension text NOT NULL,
     	count int(10) NOT NULL
     ) TYPE=MyISAM;
     
     CREATE TABLE help (
     	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	question text NOT NULL,
     	answer text NOT NULL
     ) TYPE=MyISAM;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't think you can put a number in parentheses behind TEXT,
    i think you get a 65K field in all cases

    so category text(4) would be serious overkill, man

    for fields like this, use VARCHAR, which has an upper limit of 255,
    but that should be enough

    assigning a default "zero" date is a bad move, in my humble opinion

    e.g. date_joined

    if you do a calculation for how long people have been on board,
    you will get some funny answers

    use NULL instead

    and as for your question about date formats, you must enter date values
    in year-month-day sequence, but you can display them in any format you wish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937

    I just took the default out for dates, as there probably was much point in having it in there. do you think I should keep all the NOT NULLs there? is the enums ok? and any other advice?

    thx again

    Code:
    CREATE TABLE member (
     	 id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	 username varchar(22) NOT NULL,
     	 password varchar(32) NOT NULL,
     	 email varchar(155) NOT NULL,
     	 phone varchar(15) NOT NULL,
     	 street varchar(50) NOT NULL,
     	 town varchar(50) NOT NULL,
     	 county varchar(50) NOT NULL,
     	 company varchar(40) NOT NULL,
     	 date_joined date NOT NULL,
     	 last_act datetime NOT NULL,
     	 status enum('Awaiting','Rejected','Accepted') NOT NULL default 'Awaiting',
     	 group enum('Normal','Admin','Owner') NOT NULL default 'Normal',
     	 count int(10) NOT NULL
      ) TYPE=MyISAM;
      
      CREATE TABLE article (
     	 id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	 title varchar(50) NOT NULL,
     	 version varchar(8) NOT NULL,
     	 description text NOT NULL,
     	 category varchar(10) NOT NULL,
     	 date_sub date NOT NULL,
     	 status enum('New','Updated') NOT NULL,
     	 username varchar(22) NOT NULL,
     	 location text NOT NULL,
     	 filename varchar(50) NOT NULL,
     	 extension varchar(10) NOT NULL,
     	 count int(10) NOT NULL
      ) TYPE=MyISAM;
      
      CREATE TABLE help (
     	 id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	 question text NOT NULL,
     	 answer text NOT NULL
      ) TYPE=MyISAM;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    looks okay now

    have fun

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •