Time for action - creating the MySQL database and structure

  1. Open phpMyAdmin and create a database sphinx_properties. You can use an existing database as well.
  2. Import the following SQL to create the database tables:
    CREATE TABLE IF NOT EXISTS `amenities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `amenities_properties` (
`amenity_id` int(11) NOT NULL,
`property_id` int(11) NOT NULL,
PRIMARY KEY (`amenity_id`,`property_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `properties` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` enum('1','2') NOT NULL DEFAULT '1',
`transaction_type` enum('1','2','3') NOT NULL DEFAULT '1',
`description` text NOT NULL,
`price` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`address` text NOT NULL,
`zip_code` varchar(10) NOT NULL,
`bedrooms` int(2) NOT NULL,
`bathrooms` int(2) NOT NULL,
`area` float(10,2) NOT NULL,
`built_year` int(4) NOT NULL,
`latitude` float(5,2) DEFAULT NULL,
`longitude` float(5,2) DEFAULT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What just happened?

We created a new database sphinx_properties, which will be used by our application with the following database tables:

  • amenities: This table holds the name of all the amenities related to properties. Search results can be filtered by amenities.
  • amenities_properties: This table holds the data which links a particular amenity to different properties. Each property can have multiple amenities and each amenity can be assigned to multiple properties.
  • cities: This table holds the names of cities. Each property will belong to a city and search results can be filtered by cities.
  • properties: This is the main table that holds the data related to a property. The following fields are added to this table:
    • type: Whether the property is Residential (1) or Commercial (2).
    • transaction_type: Whether the property is listed for Sale (1), Rent (2), or for a Paying Guest (3).
    • description: A description of the property.
    • price: The price of the property. (We are keeping this as an integer field.)
    • city_id: City where this property is located.
    • address and zip_code: Address of the property.
    • bedrooms, bathrooms, and area: Characteristics of the property.
    • built_year: Year in which this property was built.
    • latitude and longitude: Geo location of this property.

The following screenshots demonstrate what the database structure will look like:

amenities table:

What just happened?

amenities_properties table:

What just happened?

cities table:

What just happened?

properties table:

What just happened?

Initial data

Now that we have the database structure ready, let's populate the tables with some data.

Tip

Frontend for the application

Ideally, you would have a frontend to populate all these tables in a real world application. It is beyond the scope of this book to create a complete frontend for the application.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset