OK here is my design for the database of a site of mine. It's a site about 2nd hand cars. Only dealers will be able to add their cars to the database (after they have been added by an admin). The rest probably explains itself. I would appreciate it if you could take a good look at it and tell me if anything could improve... THANX
----------------------------------------CREATE TABLE dealers (
dealerID int(4) unsigned NOT NULL auto_increment,
name varchar(30) NOT NULL,
address varchar(50) NOT NULL,
postal_code mediumint(4) unsigned NOT NULL,
city varchar(30) NOT NULL,
tel varchar(13) NOT NULL,
fax varchar(13) NOT NULL,
url varchar(50),
email varchar(50),
last_update datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
join_date date NOT NULL,
language char(2) DEFAULT 'en' NOT NULL,
PRIMARY KEY (dealerID)
);
CREATE TABLE users (
dealerID int(4) unsigned NOT NULL,
login char(8) NOT NULL,
password char(32) NOT NULL,
level int(1) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (dealerID)
);
2nd hand cars
CREATE TABLE cars (
carID int(6) unsigned NOT NULL auto_increment,
dealerID int(4) unsigned NOT NULL,
modelID int(4) unsigned NOT NULL,
km int(6) unsigned NOT NULL,
yfr date NOT NULL,
yfr = year of first registration
ext_color varchar(10) NOT NULL,
int_color varchar(10),
price int(5) unsigned NOT NULL,
warrantyID int(1) unsigned NOT NULL,
o_metallic enum('yes','no') DEFAULT 'no',
o_leather enum('yes','no') DEFAULT 'no',
o_ac enum('yes','no') DEFAULT 'no',
o_gps enum('yes','no') DEFAULT 'no',
o_other varchar(255),
o = options
PRIMARY KEY (carID)
);
all the new models, needed for reference by table cars
CREATE TABLE models (
modelID int(4) unsigned NOT NULL auto_increment,
manufacturer varchar(25) NOT NULL,
type varchar(25) NOT NULL,
model varchar(25) NOT NULL,
cylinder int(4) unsigned NOT NULL,
kw int(3) unsigned NOT NULL,
fuel enum('p','d') DEFAULT 'p' NOT NULL,
gearbox enum('m','a') DEFAULT 'm' NOT NULL,
PRIMARY KEY (modelID)
);
CREATE TABLE warranties (
warrantyID int(1) unsigned NOT NULL auto_increment,
name tinytext,
description text,
PRIMARY KEY (warrantyID)
);
