>
> From: "Paul Fine" <[EMAIL PROTECTED]>
> Date: 2003/11/28 Fri AM 11:14:25 CST
> To: <[EMAIL PROTECTED]>
> Subject: Please analyze my project table design
>
> 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.
One quick comment---there appear to be many columns in common between the tables
representing people playing different roles (e.g. names, phone numbers).
Maybe you could create a single "person" table instead?
I don't know of a DB theory rule that says it's a bad idea to have the same columns in
many tables, but it might make the design more compact to take the common stuff and
put it into one table.
>
> 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
