Real Estate Management System Database Model

Real Estate Management System Database Model

The project entitled real estate management system is an online platform that will allow the real estate agents to post and advertise their real estate properties. Clients and customers are allowed to browse and inquire the different real estate properties posted on the platform.

This article focuses on the database design and model in order to develop a real estate management system. You are allowed to modify the database schema based on your wants and requirements. In order to support our endeavor and to help others in the development of a real estate management system a LIKE and SHARE is very much appreciated.

tbladmin (admin_id, admin_name, admin_contact, admin_address, admin_email, username, password)

Description: administrator is responsible for encoding the different property categories, the admin will also be able to monitor and moderate the comment section of the system and as well as the notification and appointment.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbladmin` (
`admin_id` int(11) NOT NULL AUTO_INCREMENT,
`admin_name` varchar(50) NOT NULL,
`admin_contact` varchar(11) NOT NULL,
`admin_address` varchar(100) NOT NULL,
`admin_email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblagent (agent_id, agent_name, agent_contact, agent_email, agent_address, agent_image, agent_fb_account, username, password)

Description: tblagent database table will store the information of real estate agents such as the name, contact, email addres, facebook account, username and password.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblagent` (
`agent_id` int(11) NOT NULL AUTO_INCREMENT,
`agent_name` varchar(50) NOT NULL,
`agent_contact` varchar(11) NOT NULL,
`agent_email` varchar(30) NOT NULL,
`agent_address` varchar(100) NOT NULL,
`agent_image` blob NOT NULL,
`agent_fb_account` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblappoinment (appointment_id, appointment_description, appointment_date, client_id, agent_id, appointment_status, admin_id)

Description: clients are allowed to set an appointment with the agents for more talks and information about a certain property. Appointment records will be stored in the tblappoinment table and it will be monitored and managed by the administrator. Clients can also cancel an appointment provided that the agent will be informed prior to the appointment date.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblappoinment` (
`appointment_id` int(11) NOT NULL AUTO_INCREMENT,
`appointment_description` varchar(100) NOT NULL,
`appointment_date` date NOT NULL,
`client_id` int(11) NOT NULL,
`agent_id` int(11) NOT NULL,
`appointment_status` int(2) NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`appointment_id`),
KEY `client_id` (`client_id`,`agent_id`,`admin_id`),
KEY `admin_id` (`admin_id`),
KEY `agent_id` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblclient (client_id, client_name, client_contact, client_email, client_address, client_image, client_fb_account, username, password)

Description: clients information such as the name, contact, email and other personal information are stored in the tblclient database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblclient` (
`client_id` int(11) NOT NULL AUTO_INCREMENT,
`client_name` varchar(50) NOT NULL,
`client_contact` varchar(11) NOT NULL,
`client_email` varchar(30) NOT NULL,
`client_address` varchar(100) NOT NULL,
`client_image` blob NOT NULL,
`client_fb_account` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcomment (comment_id, comment, property_id, client_id, comment_time, comment_date, comment_status, admin_id)

Description: comments of the clients about a certain property are also stored and recorded by the platform. Property assigned to a specific agent can view and manage the comments and conversations. Comments are being monitored and moderated by the administrator.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcomment` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`comment` varchar(100) NOT NULL,
`property_id` int(11) NOT NULL,
`client_id` int(11) NOT NULL,
`comment_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`comment_date` date NOT NULL,
`comment_status` int(2) NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `property_id` (`property_id`,`client_id`,`admin_id`),
KEY `admin_id` (`admin_id`),
KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblnotification (notification_id, notification_name, notification_description, admin_id)

Description: notification and other important updates are being encoded in the system for the information dissemination and broadcasting of messages to the clients and agents. The administrator is the one responsible for sending the said messages and information, the information can be in a form of a text or an email.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblnotification` (
`notification_id` int(11) NOT NULL AUTO_INCREMENT,
`notification_name` varchar(30) NOT NULL,
`notification_description` varchar(100) NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`notification_id`),
KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblproperty (property_id, property_name, description, price, property_type_id, agent_id, property_status)

Description: property information and details such as the price and category is stored in the tblproperty database table. Encoding of property information are part and job of the agent, they are the one responsible for updating the property information and its availability.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblproperty` (
`property_id` int(11) NOT NULL AUTO_INCREMENT,
`property_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`price` float NOT NULL,
`property_type_id` int(11) NOT NULL,
`agent_id` int(11) NOT NULL,
`property_status` int(2) NOT NULL,
PRIMARY KEY (`property_id`),
KEY `property_type_id` (`property_type_id`,`agent_id`),
KEY `agent_id` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpropertyimage (property_image_id, image_name, image_description, property_id)

Description: the agents are allowed to post or upload multiple images of the property that’s why tblpropertyimage was created for that purpose.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpropertyimage` (
`property_image_id` int(11) NOT NULL AUTO_INCREMENT,
`image_name` varchar(50) NOT NULL,
`image_description` varchar(100) NOT NULL,
`property_id` int(11) NOT NULL,
PRIMARY KEY (`property_image_id`),
KEY `property_id` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpropertytype (property_type_id, type_name, type_description, admin_id)

Description: the administrator will encode first the list of property categories before an agent can encode the other information of a property.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpropertyimage` (
`property_image_id` int(11) NOT NULL AUTO_INCREMENT,
`image_name` varchar(50) NOT NULL,
`image_description` varchar(100) NOT NULL,
`property_id` int(11) NOT NULL,
PRIMARY KEY (`property_image_id`),
KEY `property_id` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

—
— Constraints for table `tblappoinment`
—
ALTER TABLE `tblappoinment`
ADD CONSTRAINT `tblappoinment_ibfk_3` FOREIGN KEY (`agent_id`) REFERENCES `tblagent` (`agent_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblappoinment_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblappoinment_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `tblclient` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcomment`
—
ALTER TABLE `tblcomment`
ADD CONSTRAINT `tblcomment_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `tblclient` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblcomment_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblcomment_ibfk_2` FOREIGN KEY (`property_id`) REFERENCES `tblproperty` (`property_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tblnotification`
—
ALTER TABLE `tblnotification`
ADD CONSTRAINT `tblnotification_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tblproperty`
—
ALTER TABLE `tblproperty`
ADD CONSTRAINT `tblproperty_ibfk_2` FOREIGN KEY (`property_type_id`) REFERENCES `tblpropertytype` (`property_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblproperty_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `tblagent` (`agent_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpropertyimage`
—
ALTER TABLE `tblpropertyimage`
ADD CONSTRAINT `tblpropertyimage_ibfk_1` FOREIGN KEY (`property_id`) REFERENCES `tblproperty` (`property_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tblpropertytype`
—
ALTER TABLE `tblpropertytype`
ADD CONSTRAINT `tblpropertytype_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.