Greetings, my "hands on" school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w/InnoDB at some point.
I would greatly appreciate any feedback on this design.
These are the business rules:
1) 1 matter may have several vendors and/or several purchasers
2) 1 matter will have 1 lawyer and 1 agent
Thank you for any advice!
CREATE TABLE other_lawyers (
other_lawyer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
other_lawyer_fname VARCHAR(25) NULL,
other_lawyer_lname VARCHAR(25) NULL,
other_lawyer_email VARCHAR(25) NULL,
other_lawyer_address VARCHAR(50) NULL,
other_lawyer_city VARCHAR(15) NULL,
other_lawyer_provice VARCHAR(15) NULL,
other_lawyer_postal VARCHAR(6) NULL,
other_lawyer_phone VARCHAR(10) NULL,
other_lawyer_fax VARCHAR(10) NULL,
other_lawyer_firm VARCHAR(40) NULL,
PRIMARY KEY(other_lawyer_id)
);
CREATE TABLE agents (
agent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
agent_fname VARCHAR(25) NULL,
agent_lname VARCHAR(25) NULL,
agent_email VARCHAR(25) NULL,
agent_address VARCHAR(50) NULL,
agent_city VARCHAR(15) NULL,
agent_provice VARCHAR(15) NULL,
agent_postal VARCHAR(6) NULL,
agent_phone VARCHAR(10) NULL,
agent_fax VARCHAR(10) NULL,
agent_agency VARCHAR(40) NULL,
PRIMARY KEY(agent_id)
);
CREATE TABLE matters (
file_number SMALLINT UNSIGNED NOT NULL,
agents_agent_id INTEGER UNSIGNED NOT NULL,
other_lawyers_other_lawyer_id INTEGER UNSIGNED NOT NULL,
sale_or_purchase ENUM('s','p') NULL,
property_address VARCHAR(50) NULL,
property_city VARCHAR(15) NULL,
property_province VARCHAR(15) NULL,
price FLOAT(8,2) NULL,
file_open_date DATE NULL,
file_posession_date DATE NULL,
PRIMARY KEY(file_number),
INDEX matters_FKIndex1(other_lawyers_other_lawyer_id),
INDEX matters_FKIndex2(agents_agent_id)
);
CREATE TABLE vendors (
vendor_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
vendor_fname VARCHAR(25) NULL,
vendor_lname VARCHAR(25) NULL,
vendor_email VARCHAR(25) NULL,
vendor_address VARCHAR(50) NULL,
vendor_city VARCHAR(15) NULL,
vendor_provice VARCHAR(15) NULL,
vendor_phone_home VARCHAR(10) NULL,
vendor_phone_work VARCHAR(10) NULL,
vendor_postal VARCHAR(6) NULL,
vendor_fax VARCHAR(10) NULL,
vendor_firm VARCHAR(40) NULL,
PRIMARY KEY(vendor_id),
INDEX vendors_FKIndex1(matters_file_number)
);
CREATE TABLE purchasers (
purchasers_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
purchaser_fname VARCHAR(25) NULL,
purchaser_lname VARCHAR(25) NULL,
purchaser_email VARCHAR(25) NULL,
purchaser_address VARCHAR(50) NULL,
purchaser_city VARCHAR(15) NULL,
purchaser_provice VARCHAR(15) NULL,
purchaser_phone_home VARCHAR(10) NULL,
purchaser_phone_work VARCHAR(10) NULL,
purchaser_postal VARCHAR(6) NULL,
purchaser_fax VARCHAR(10) NULL,
purchaser_firm VARCHAR(40) NULL,
PRIMARY KEY(purchasers_id),
INDEX purchasers_FKIndex1(matters_file_number)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
