Forum Captain'sLog Crewlist CrewWanted BoatForsale Classifieds Boat Sailor


ALL Boards
  640
News And
Events
  68
Regulations
  18
Racing
  14
Cruising &
Liveaboard
  162
Multihulls
  15
Building &
Repairs
  15
Outfitting
& Gear
  22
7knots FAQ
& General
  51
Development
Bug Reports
  22
Misc & Other
  62
Test Posts
  191
ID: 13 Title: 7k Tables And Data Structures Replis: 0 Read: 1094 Author: 1
Name: Tom Yang  Posts: 110    Vancouver Time: 2003-1-29_5:11:21 Quote    Reply
There are 6 tables holding all 7k's data inside MySQL engine.
Sailor, Boat, Posrpt(captain's log), Gear, YP(Links), Forum:

CREATE TABLE sailor (
  sailor_id mediumint(8) unsigned NOT NULL auto_increment,
  email varchar(60) NOT NULL default '',
  password varchar(50) NOT NULL default '',
  sailor_flag tinyint(4) NOT NULL default '0',
  name varchar(40) NOT NULL default 'N/A',
  orig_email varchar(60) default NULL,
  photo_url varchar(150) default NULL,
  website varchar(100) default NULL,
  description text,
  city varchar(40) NOT NULL default '',
  state varchar(40) NOT NULL default '',
  country varchar(40) NOT NULL default '',
  crewlist_flag tinyint(1) NOT NULL default '0',
  gender varchar(10) default NULL,
  age varchar(15) default NULL,
  nationality varchar(40) default NULL,
  subject varchar(56) default NULL,
  email_flag tinyint(1) NOT NULL default '0',
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  activity varchar(15) default NULL,
  counter smallint(5) unsigned NOT NULL default '0',
  ip varchar(15) default NULL,
  real_password varchar(50) default NULL,
  home_boy tinyint(1) NOT NULL default '0',
  post_cnt smallint(5) unsigned default NULL,
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (sailor_id),
  UNIQUE KEY email (email),
  FULLTEXT KEY des_sailor (description),
  KEY gender (gender),
  KEY act_sailor (activity)
) TYPE=MyISAM;

CREATE TABLE boat (
  boat_id smallint(5) unsigned NOT NULL auto_increment,
  sailor_id mediumint(8) unsigned NOT NULL default '0',
  name varchar(40) default NULL,
  design varchar(25) default NULL,
  type varchar(20) default NULL,
  photo_url varchar(150) default NULL,
  website varchar(100) default NULL,
  city varchar(40) NOT NULL default '',
  state varchar(40) NOT NULL default '',
  country varchar(40) NOT NULL default '',
  homeport varchar(80) default NULL,
  crew varchar(100) default NULL,
  description text,
  forsale_flag tinyint(1) NOT NULL default '0',
  price varchar(20) default NULL,
  currency varchar(30) default NULL,
  us_price int(10) unsigned default NULL,
  year year(4) default NULL,
  hull varchar(20) default NULL,
  engine varchar(25) default NULL,
  loa decimal(8,1) default NULL,
  lwl varchar(16) default NULL,
  beam varchar(16) default NULL,
  draft varchar(16) default NULL,
  disp varchar(16) default NULL,
  ballast varchar(16) default NULL,
  fuel varchar(10) default NULL,
  water varchar(10) default NULL,
  dinghy varchar(50) default NULL,
  outboard varchar(40) default NULL,
  ip varchar(15) default NULL,
  skipper_flag tinyint(1) NOT NULL default '0',
  subject varchar(56) default NULL,
  status_flag smallint(5) default NULL,
  not_used3 varchar(128) default NULL,
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  activity varchar(25) default NULL,
  departure date default NULL,
  counter smallint(5) unsigned NOT NULL default '0',
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (boat_id),
  FULLTEXT KEY des_boat (description),
  KEY type (type),
  KEY act_boat (activity)
) TYPE=MyISAM;

CREATE TABLE posrpt (
  posrpt_id smallint(5) unsigned NOT NULL auto_increment,
  boat_id smallint(5) unsigned NOT NULL default '0',
  sailor_id mediumint(8) unsigned NOT NULL default '0',
  subject varchar(50) NOT NULL default '',
  latitude decimal(8,3) NOT NULL default '0.000',
  longitude decimal(8,3) NOT NULL default '0.000',
  description text,
  ip varchar(128) default NULL,
  date date NOT NULL default '0000-00-00',
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  boatname varchar(40) default NULL,
  sailorname varchar(40) default NULL,
  region varchar(30) default NULL,
  sender varchar(40) default NULL,
  link_flag tinyint(1) NOT NULL default '0',
  counter smallint(5) unsigned NOT NULL default '0',
  mime_type varchar(10) default NULL,
  caption varchar(40) default NULL,
  status_flag smallint(5) default NULL,
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (posrpt_id),
  FULLTEXT KEY des_posrpt (description)
) TYPE=MyISAM;

CREATE TABLE gear (
  gear_id smallint(5) unsigned NOT NULL auto_increment,
  sailor_id mediumint(8) unsigned NOT NULL default '0',
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  subject varchar(50) NOT NULL default '',
  price varchar(12) default NULL,
  category varchar(30) default NULL,
  city varchar(40) NOT NULL default '',
  state varchar(40) NOT NULL default '',
  country varchar(40) NOT NULL default '',
  currency varchar(25) default NULL,
  us_price mediumint(8) unsigned default NULL,
  photo_url varchar(150) default NULL,
  description text,
  ip varchar(128) default NULL,
  counter smallint(5) unsigned NOT NULL default '0',
  status_flag smallint(5) default NULL,
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (gear_id),
  FULLTEXT KEY des_gear (description),
  KEY cat_gear (category)
) TYPE=MyISAM;

CREATE TABLE yp (
  yp_id smallint(5) unsigned NOT NULL auto_increment,
  sailor_id mediumint(8) unsigned NOT NULL default '0',
  subject varchar(60) default NULL,
  category varchar(40) NOT NULL default '',
  city varchar(40) NOT NULL default '',
  state varchar(40) NOT NULL default '',
  country varchar(40) NOT NULL default '',
  ip varchar(15) default NULL,
  website varchar(100) default NULL,
  url_flag tinyint(1) default NULL,
  phone varchar(25) default NULL,
  description varchar(255) default NULL,
  not_used1 varchar(128) default NULL,
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  counter smallint(5) unsigned NOT NULL default '0',
  status_flag smallint(5) default NULL,
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (yp_id),
  KEY cat_yp (category)
) TYPE=MyISAM;
 
CREATE TABLE forum (
  forum_id mediumint(8) unsigned NOT NULL auto_increment,
  sailor_id mediumint(8) unsigned NOT NULL default '0',
  subject varchar(60) default NULL,
  board varchar(25) default NULL,
  header text,
  description text,
  reply_cnt tinyint(3) unsigned NOT NULL default '0',
  visit_cnt smallint(5) unsigned NOT NULL default '0',
  author_cnt tinyint(3) unsigned NOT NULL default '0',
  author_list varchar(255) default NULL,
  ip varchar(15) default NULL,
  touchup datetime NOT NULL default '0000-00-00 00:00:00',
  no_back mediumint(8) unsigned default NULL,
  genesis date NOT NULL default '0000-00-00',
  PRIMARY KEY  (forum_id),
  FULLTEXT KEY des_forum (description),
  KEY board (board)
) TYPE=MyISAM;