   creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   /*usr_inf_profile_picture, ME: make this a blob?*/
   /*Please check whether this sytax with primary and foreign keys named the same may give DB trouble*/
-   PRIMARY KEY (usr_id),
+  PRIMARY KEY (usr_id),
   CONSTRAINT FK_UI_usr_id FOREIGN KEY(usr_id) REFERENCES User(usr_id)
 );
 
 CREATE TABLE ResoProperty (
-reso_property_key INTEGER NOT NULL AUTO_INCREMENT,
-reso_reso_ListingId VARCHAR(255) NOT NULL,
-reso_SourceSystemName VARCHAR(255) NOT NULL DEFAULT 'MA_MLS',
-reso_Appliances VARCHAR(1024),
-reso_AssociationFee DECIMAL(14,2),
-reso_AssociationName VARCHAR(50),
-reso_AssociationYN BOOLEAN DEFAULT FALSE,
-reso_BathroomsFull TINYINT,
-reso_BathroomsHalf TINYINT,
-reso_BedroomsTotal TINYINT,
-reso_BuyerAgencyCompensation VARCHAR(25),
-reso_BuyerAgencyCompensationType ENUM('G','N') DEFAULT 'G',
-/* these MAMLS values will have to be changed when scaling*/
-reso_BuyerAgentAOR ENUM('Massachusetts Assocition of Realtors'),
-/* these MAMLS values will have to be changed when scaling*/
-reso_BuyerAgentCellPhone VARCHAR(16),
-reso_BuyerAgentEmail VARCHAR(80),
-reso_BuyerAgentFirstName VARCHAR(50),
-reso_BuyerAgentFullName VARCHAR(150),
-reso_BuyerAgentLastName VARCHAR(50),
-reso_BuyerAgentMlsId VARCHAR(25),
-reso_BuyerAgentNameSuffix VARCHAR(10),
-reso_BuyerAgentStateLicense VARCHAR(50),
-reso_City VARCHAR(50),
-reso_CityRegion VARCHAR(150),
-reso_Contingency VARCHAR(1024),
-reso_Disclosures VARCHAR(4000),
-reso_DocumentsAvailable VARCHAR(1024),
-reso_DocumentsCount TINYINT,
-reso_Exclusions VARCHAR(1024),
-reso_ListAgentAOR ENUM('Massachusetts Assocition of Realtors'),
-/* these MAMLS values will have to be changed when scaling*/
-reso_ListAgentCellPhone VARCHAR(16),
-reso_ListAgentEmail VARCHAR(80),
-reso_ListAgentFirstName VARCHAR(50),
-reso_ListAgentFullName VARCHAR(150),
-reso_ListAgentKey VARCHAR(255),
-reso_ListAgentKeyNumeric VARCHAR(255),
-reso_ListAgentLastName VARCHAR(50),
-reso_ListAgentMiddleName VARCHAR(50),
-reso_ListAgentMlsId VARCHAR(25),
-reso_ListAgentNameSuffix VARCHAR(10),
-reso_ListAgentOfficePhone VARCHAR(16),
-reso_ListAgentStateLicense VARCHAR(50),
-reso_ListPrice DECIMAL(14,2),
-reso_ListingAgreement ENUM('M','A','D','E'),
-/* these MAMLS values will have to be changed when scaling*/
-reso_ListingKey VARCHAR(255),
-reso_ListingService ENUM('N','Y'),
-reso_LivingArea DECIMAL(14,2),
-reso_LotSizeAcres DECIMAL(16,4),
-reso_LotSizeSquareFeet DECIMAL(14,2),
-reso_NumberOfUnitsInCommunity SMALLINT,
-reso_NumberOfUnitsLeased SMALLINT,
-reso_NumberOfUnitsTotal TINYINT,
-reso_OnMarketDate DATE,
-reso_OwnerName VARCHAR(50),
-reso_OwnerPhone VARCHAR(16),
-reso_Ownership VARCHAR(1024),
-reso_ParcelNumber VARCHAR(50),
-reso_ParkingTotal DECIMAL(14,2),
-reso_PostalCode VARCHAR(10),
-reso_PostalCodePlus4 VARCHAR(4),
-reso_PrivateRemarks VARCHAR(4000),
-reso_PropertySubType ENUM('CI','MF','CC','RN','SF','BU','GEN') DEFAULT 'GEN',
-/* these MAMLS values will have to be changed when scaling*/
-reso_PropertyType ENUM('GEN'),
-reso_PublicRemarks VARCHAR(4000),
-reso_SpecialListingConditions VARCHAR(1024),
-reso_StandardStatus ENUM('ACT','UAG','PCG','CTG','BOM','NEW'),
-/* these MAMLS values will have to be changed when scaling*/
-reso_StatusChangeTimestamp TIMESTAMP,
-reso_StreetName VARCHAR(50),
-reso_StreetNumber VARCHAR(25),
-reso_StreetSuffix VARCHAR(25),
-reso_SubAgencyCompensation VARCHAR(25),
-reso_SubAgencyCompensationType ENUM('G','N') DEFAULT 'G',
-/* these MAMLS values will have to be changed when scaling*/
-reso_TaxBookNumber VARCHAR(25),
-reso_TaxParcelLetter VARCHAR(25),
-reso_TransactionBrokerCompensation VARCHAR(25),
-reso_TransactionBrokerCompensationType ENUM('G','N') DEFAULT 'G',
-/* these MAMLS values will have to be changed when scaling*/
-reso_DualVariableCompensationYN BOOLEAN DEFAULT FALSE,
-reso_UnitNumber VARCHAR(25),
-reso_YearBuilt SMALLINT,
-reso_LeadPaint VARCHAR(32),
-mamls_MANAGEMENT_CC VARCHAR(32),
-mamls_MASTER_BOOK_CC INTEGER,
-mamls_MASTER_PAGE_CC INTEGER,
-mamls_NO_UNITS_OWNER_OCC_CC SMALLINT,
-last_edit_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-PRIMARY KEY (reso_property_key)
+  reso_property_key INTEGER NOT NULL AUTO_INCREMENT,
+  reso_reso_ListingId VARCHAR(255) NOT NULL,
+  reso_SourceSystemName VARCHAR(255) NOT NULL DEFAULT 'MA_MLS',
+  reso_Appliances VARCHAR(1024),
+  reso_AssociationFee DECIMAL(14,2),
+  reso_AssociationName VARCHAR(50),
+  reso_AssociationYN BOOLEAN DEFAULT FALSE,
+  reso_BathroomsFull TINYINT,
+  reso_BathroomsHalf TINYINT,
+  reso_BedroomsTotal TINYINT,
+  reso_BuyerAgencyCompensation VARCHAR(25),
+  reso_BuyerAgencyCompensationType ENUM('G','N') DEFAULT 'G',
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_BuyerAgentAOR ENUM('Massachusetts Assocition of Realtors'),
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_BuyerAgentCellPhone VARCHAR(16),
+  reso_BuyerAgentEmail VARCHAR(80),
+  reso_BuyerAgentFirstName VARCHAR(50),
+  reso_BuyerAgentFullName VARCHAR(150),
+  reso_BuyerAgentLastName VARCHAR(50),
+  reso_BuyerAgentMlsId VARCHAR(25),
+  reso_BuyerAgentNameSuffix VARCHAR(10),
+  reso_BuyerAgentStateLicense VARCHAR(50),
+  reso_City VARCHAR(50),
+  reso_CityRegion VARCHAR(150),
+  reso_Contingency VARCHAR(1024),
+  reso_Disclosures VARCHAR(4000),
+  reso_DocumentsAvailable VARCHAR(1024),
+  reso_DocumentsCount TINYINT,
+  reso_Exclusions VARCHAR(1024),
+  reso_ListAgentAOR ENUM('Massachusetts Assocition of Realtors'),
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_ListAgentCellPhone VARCHAR(16),
+  reso_ListAgentEmail VARCHAR(80),
+  reso_ListAgentFirstName VARCHAR(50),
+  reso_ListAgentFullName VARCHAR(150),
+  reso_ListAgentKey VARCHAR(255),
+  reso_ListAgentKeyNumeric VARCHAR(255),
+  reso_ListAgentLastName VARCHAR(50),
+  reso_ListAgentMiddleName VARCHAR(50),
+  reso_ListAgentMlsId VARCHAR(25),
+  reso_ListAgentNameSuffix VARCHAR(10),
+  reso_ListAgentOfficePhone VARCHAR(16),
+  reso_ListAgentStateLicense VARCHAR(50),
+  reso_ListPrice DECIMAL(14,2),
+  reso_ListingAgreement ENUM('M','A','D','E'),
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_ListingKey VARCHAR(255),
+  reso_ListingService ENUM('N','Y'),
+  reso_LivingArea DECIMAL(14,2),
+  reso_LotSizeAcres DECIMAL(16,4),
+  reso_LotSizeSquareFeet DECIMAL(14,2),
+  reso_NumberOfUnitsInCommunity SMALLINT,
+  reso_NumberOfUnitsLeased SMALLINT,
+  reso_NumberOfUnitsTotal TINYINT,
+  reso_OnMarketDate DATE,
+  reso_OwnerName VARCHAR(50),
+  reso_OwnerPhone VARCHAR(16),
+  reso_Ownership VARCHAR(1024),
+  reso_ParcelNumber VARCHAR(50),
+  reso_ParkingTotal DECIMAL(14,2),
+  reso_PostalCode VARCHAR(10),
+  reso_PostalCodePlus4 VARCHAR(4),
+  reso_PrivateRemarks VARCHAR(4000),
+  reso_PropertySubType ENUM('CI','MF','CC','RN','SF','BU','GEN') DEFAULT 'GEN',
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_PropertyType ENUM('GEN'),
+  reso_PublicRemarks VARCHAR(4000),
+  reso_SpecialListingConditions VARCHAR(1024),
+  reso_StandardStatus ENUM('ACT','UAG','PCG','CTG','BOM','NEW'),
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_StatusChangeTimestamp TIMESTAMP,
+  reso_StreetName VARCHAR(50),
+  reso_StreetNumber VARCHAR(25),
+  reso_StreetSuffix VARCHAR(25),
+  reso_SubAgencyCompensation VARCHAR(25),
+  reso_SubAgencyCompensationType ENUM('G','N') DEFAULT 'G',
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_TaxBookNumber VARCHAR(25),
+  reso_TaxParcelLetter VARCHAR(25),
+  reso_TransactionBrokerCompensation VARCHAR(25),
+  reso_TransactionBrokerCompensationType ENUM('G','N') DEFAULT 'G',
+  /* these MAMLS values will have to be changed when scaling*/
+  reso_DualVariableCompensationYN BOOLEAN DEFAULT FALSE,
+  reso_UnitNumber VARCHAR(25),
+  reso_YearBuilt SMALLINT,
+  reso_LeadPaint VARCHAR(32),
+  mamls_MANAGEMENT_CC VARCHAR(32),
+  mamls_MASTER_BOOK_CC INTEGER,
+  mamls_MASTER_PAGE_CC INTEGER,
+  mamls_NO_UNITS_OWNER_OCC_CC SMALLINT,
+  last_edit_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (reso_property_key)
 );
 
 CREATE TABLE Preference (

  doc_size DECIMAL(12,2) NOT NULL,
  creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (doc_id),
  CONSTRAINT FK_doc_ofr_id FOREIGN KEY(ofr_id) REFERENCES User(ofr_id),
  CONSTRAINT FK_Doc_usr_id FOREIGN KEY(usr_id) REFERENCES Offer(usr_id),
  CONSTRAINT FK_Doc_ft_id FOREIGN KEY(ft_id) REFERENCES FormType(ft_id)
);


CREATE INDEX User_Name ON UserInfo(usr_inf_suffix,usr_inf_first_name,usr_inf_middle_name,usr_inf_last_name);

CREATE INDEX User_Address ON UserInfo(usr_inf_addr_street1,usr_inf_addr_street2,usr_inf_addr_apt_num,usr_inf_addr_zip,usr_inf_addr_city,usr_inf_addr_state);

CREATE INDEX User_Email ON UserInfo(usr_inf_email_verified);

CREATE INDEX User_Phone ON UserInfo(usr_inf_phone_verified);

CREATE INDEX User_Details ON UserInfo(usr_inf_suffix,usr_inf_first_name,usr_inf_middle_name,usr_inf_last_name,usr_inf_addr_street1,usr_inf_addr_street2,usr_inf_addr_apt_num,usr_inf_addr_zip,usr_inf_addr_city,usr_inf_addr_state,usr_inf_email_verified,usr_inf_phone_verified);

CREATE INDEX ResoBuyerAgent_Details ON ResoProperty(reso_BuyerAgentNameSuffix ,reso_BuyerAgentFirstName,reso_BuyerAgentLastName,reso_BuyerAgentCellPhone,reso_BuyerAgentEmail);

CREATE INDEX ResoListAgent_Details ON ResoProperty(reso_ListAgentNameSuffix,reso_ListAgentFirstName,reso_ListAgentMiddleName ,reso_ListAgentFirstName,reso_ListAgentOfficePhone,reso_ListAgentCellPhone,reso_ListAgentEmail);

CREATE INDEX ResoProperty_Address ON ResoProperty(reso_StreetName,reso_StreetNumber,reso_StreetSuffix);

/*CREATE INDEX Property_Address ON Property(prop_street_name,prop_street_num,prop_city,rop_state,prop_zip);*/

  CONSTRAINT FK_Pref_usr_id FOREIGN KEY(usr_id) REFERENCES User(usr_id)
);

CREATE TABLE Property (
  prop_id INTEGER AUTO_INCREMENT,
  reso_property_key INTEGER,
