List:General Discussion« Previous MessageNext Message »
From:Paul Fine Date:November 28 2003 5:14pm
Subject:Please analyze my project table design
View as plain text  
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)
);


Thread
Please analyze my project table designPaul Fine28 Nov
Re: Please analyze my project table designvze2spjf28 Nov
  • Re: Please analyze my project table designDavid T-G28 Nov
    • RE: Please analyze my project table designPaul F28 Nov
      • Re[2]: Please analyze my project table designStefan Hinz29 Nov