database design : 3 month later

Damien COLA Sun, 30 Mar 2003 07:48:47 -0800

Hello, I was starting a php/mysql project in december and seeked for help on
this mailing list, it has been a great help for solving problems.
My application is a website where you can order meals from restaurants that
are within your postcode area (UK only)


The database structure has grown up to 30 tables. My biggest project yet.
I would like to validate the database structure to SQL experts, in
particular regarding mySQL indexes that I am not sure if it'll help or not,
as there should be lots of select query while browsing the
restaurants/menus.
And I wonder how it will manage when there'll be 1000 restaurants or more.

Here the sql dump of the main tables, please don't get scared ;-)

CREATE TABLE `tgl_address` (
  `addressID` int(11) NOT NULL auto_increment,
  `address` varchar(255) NOT NULL default '',
  `postcode` varchar(10) NOT NULL default '',
  `city` varchar(30) NOT NULL default 'London',
  `website` varchar(255) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `phone` varchar(20) NOT NULL default '',
  `fax` varchar(20) NOT NULL default '',
  `sms` varchar(20) NOT NULL default '',
  `contact` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`addressID`),
  UNIQUE KEY `addressID` (`addressID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_basket` (
  `basketID` int(11) NOT NULL auto_increment,
  `status` smallint(1) NOT NULL default '0',
  `delivery_takeaway` varchar(100) NOT NULL default '',
  `paymode` varchar(50) NOT NULL default '',
  `delivery_time` varchar(50) NOT NULL default '',
  `comment` varchar(255) NOT NULL default '',
  `added_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `totalprice` decimal(4,2) NOT NULL default '0.00',
  `userID` int(11) NOT NULL default '0',
  `restID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`basketID`),
  UNIQUE KEY `basketID` (`basketID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_basket_item` (
  `basket_itemID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `comment` varchar(255) NOT NULL default '',
  `price` decimal(4,2) NOT NULL default '0.00',
  `itemID` int(11) NOT NULL default '0',
  `basketID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`basket_itemID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_basket_item_size` (
  `basket_item_sizeID` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` float(4,2) NOT NULL default '0.00',
  `coef_topping` float(6,4) NOT NULL default '1.0000',
  `itemID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`basket_item_sizeID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_card` (
  `cardID` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  `description` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`cardID`),
  UNIQUE KEY `styleID` (`cardID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_course` (
  `courseID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `image` varchar(100) NOT NULL default '',
  `rank` tinyint(3) unsigned NOT NULL default '0',
  `restID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`courseID`),
  UNIQUE KEY `typeID` (`courseID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_item` (
  `itemID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` decimal(4,2) NOT NULL default '0.00',
  `single_choice` tinyint(1) NOT NULL default '0',
  `courseID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`itemID`),
  UNIQUE KEY `mealID` (`itemID`),
  KEY `itemID` (`itemID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_item_size` (
  `item_sizeID` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` float(4,2) NOT NULL default '0.00',
  `coef_topping` float(6,4) NOT NULL default '1.0000',
  `itemID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`item_sizeID`),
  KEY `itemID` (`itemID`),
  KEY `itemID_2` (`itemID`),
  KEY `itemID_3` (`itemID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_paymode` (
  `paymodeID` int(11) NOT NULL auto_increment,
  `paymode` varchar(255) NOT NULL default '',
  `rank` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`paymodeID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_postcode` (
  `postcodeID` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL default '',
  `description` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`postcodeID`),
  UNIQUE KEY `styleID` (`postcodeID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_basket_item_topping` (
  `basket_itemID` int(11) NOT NULL default '0',
  `toppingID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`basket_itemID`,`toppingID`),
  KEY `basket_itemID` (`basket_itemID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_item_topping` (
  `itemID` int(11) NOT NULL default '0',
  `toppingID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`itemID`,`toppingID`),
  KEY `itemID` (`itemID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_rest_card` (
  `restID` int(11) NOT NULL default '0',
  `cardID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`restID`,`cardID`),
  KEY `restID` (`restID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_rest_paymode` (
  `restID` int(11) NOT NULL default '0',
  `paymodeID` int(11) NOT NULL default '0',
  KEY `restID` (`restID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_rest_postcode` (
  `restID` int(11) NOT NULL default '0',
  `postcodeID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`restID`,`postcodeID`),
  KEY `restID` (`restID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rel_rest_time` (
  `restID` int(11) NOT NULL default '0',
  `timeID` int(11) NOT NULL default '0',
  `weekday` tinyint(4) NOT NULL default '0',
  KEY `restID` (`restID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_rest` (
  `restID` int(11) NOT NULL auto_increment,
  `active` tinyint(1) NOT NULL default '1',
  `name` varchar(255) NOT NULL default '',
  `istakeaway` char(1) NOT NULL default '1',
  `isdelivery` char(1) NOT NULL default '1',
  `description` text NOT NULL,
  `delivery_time` tinyint(3) NOT NULL default '30',
  `delivery_charge` decimal(4,2) NOT NULL default '0.00',
  `delivery_min_order` decimal(4,2) NOT NULL default '0.00',
  `img_badge` varchar(255) NOT NULL default '',
  `img_picture` varchar(255) NOT NULL default '',
  `openinghours` varchar(255) NOT NULL default '',
  `addressID` int(11) NOT NULL default '0',
  `styleID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`restID`),
  UNIQUE KEY `restaurantID` (`restID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_skin` (
  `skinID` int(11) NOT NULL auto_increment,
  `active` tinyint(1) NOT NULL default '1',
  `ad` varchar(100) NOT NULL default '',
  `ad_url` varchar(100) NOT NULL default '',
  `logo` varchar(100) NOT NULL default '',
  `web` varchar(100) NOT NULL default '',
  `prim_col1` varchar(6) NOT NULL default '',
  `prim_col2` varchar(6) NOT NULL default '',
  `prim_col3` varchar(6) NOT NULL default '',
  `prim_col4` varchar(6) NOT NULL default '',
  `dark_col1` varchar(6) NOT NULL default '',
  `dark_col2` varchar(6) NOT NULL default '',
  `corner1` varchar(100) NOT NULL default '',
  `corner2` varchar(100) NOT NULL default '',
  `scrolltop1` varchar(100) NOT NULL default '',
  `scrolltop2` varchar(100) NOT NULL default '',
  `restID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`skinID`),
  UNIQUE KEY `styleID` (`skinID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_style` (
  `styleID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `photo` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`styleID`),
  UNIQUE KEY `styleID` (`styleID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_time` (
  `timeID` int(11) NOT NULL auto_increment,
  `time` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`timeID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_topping` (
  `toppingID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` float(4,2) NOT NULL default '0.00',
  `courseID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`toppingID`)
) TYPE=MyISAM;

CREATE TABLE `tgl_user` (
  `userID` int(11) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL default '',
  `lastname` varchar(50) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `postcode` varchar(10) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `mobile` varchar(30) NOT NULL default '',
  `subscribe` tinyint(1) NOT NULL default '1',
  `referer` varchar(100) NOT NULL default '',
  `added_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `city` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`userID`),
  UNIQUE KEY `userID` (`userID`)
) TYPE=MyISAM;

That's it, I've deleted some tables that have nothing original
Cheers !

Damien



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

database design : 3 month later

Reply via email to