Damien,
1. If a column is a primary key, there is no need to declare it a unique.
2. Without foreign keys, your CREATE statements don't reveal table
relationships.
3. The best way I know to validate a schema is to (i) write or draw all the
application's use cases, (ii) from the use cases derive all the required
data items, (iii) organise these items into a structure diagram with a
modelling tool like Microsoft Visio or Dezign, (iv) have the modelling tool
generate the database, (v) populate the database with a bit of test data and
(vi) see if you can derive (with or without pseudocode) all the system's
required outputs. The crucial tests are step (iv) anf (vi).
PB
[filter fodder: mySQL]
-----
----- Original Message -----
From: Damien COLA
To: [EMAIL PROTECTED]
Sent: Sunday, March 30, 2003 9:49 AM
Subject: database design : 3 month later
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;
<snip>
