Database Design Project for Real Estate

Database Design Project for Real Estate

dheerajy's photo
dheerajy
·

10 min read

Table of contents

In this article, I will be following the Database Star YT video for Designing a database.

Just creating a database schema nothing fancy, like I am not creating constraints on tables and inserting data.

Designing a Database for a Real Estate company responsible for

  1. Listing properties for sale or for rent,

  2. Keeping track of people who show interest and

  3. Attend inspections,

  4. Recording sales

Let's view each requirement and see how to design for it.

Create Database,

  •         create database realestate
        on primary
            (
                name = N'Realest_data',
                filename = N'E:\Realest.mdf',
                size = 8192kb,
                filegrowth = 65536kb
            )
        Log on
            (
                name = N'Realest_log',
                filename = N'E:\Realest_log.ldf',
                size = 8192kb,
                filegrowth = 65536kb
            )

1. Requirement: Property

Capture information About properties:

  • address,

  • type of property,

  • size of the property, size of the block for the house number of bedrooms and bathrooms and car spaces come up under the description.

i. Property Table

DBML (Database Markup Language),

  •         Table property {
          id int pk
          address_line varchar
          address_line2 varchar
          city varchar
          region varchar
          property_type_id int
          property_size int
          block_size int
          num_bedrooms int
          num_bathrooms int
          num_carspaces int
          description varchar
        }

Adding Foreign key to property_type_id column of Property table

  • Ref: property.property_type_id > Property_type.id

SQL Server,

  •         create table property
            (
                id int,
                address_line varchar(20),
                address_line2 varchar(20),
                city varchar(20),
                region varchar(20),
                property_type_id int,
                property_size int,
                block_size int,
                num_bedrooms int,
                num_bathrooms int,
                num_carspaces int,
                description varchar(20),
                constraint pk_property_id
                primary key (id),
                constraint fk_Property_type_id
                foreign key (property_type_id) references Property_type(id),
            )

ii. Property_type Table

DBML

  •          Table Property_type{
          id int pk
          description varchar
          Note: 'Examples: house, Unit, Townhouse'
        }

SQL Server,

  •         create table Property_type
            (
                id int,
                description varchar(20), 
                --Note: 'Examples: house, Unit, Townhouse'
                constraint pk_Property_type_id
                primary key (id)
            )
        go

Add table description,

  •         exec sp_addextendedproperty
        @name = N'Table_Description',
        @value = N'Note:'Examples: house, Unit, Townhouse'',
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'Table',  @level1name = 'Property_type';
        go

        /*To view extendedproperty*/
        select *
        from sys.extended_properties
        where major_id = OBJECT_ID('Property_type')

        /*To drop extendedproperty*/
        exec sp_dropextendedproperty 
        @name = N'Table_Description',
        @level0type = N'Schema', @level0name = 'dbo',
        @level1type = N'Table',  @level1name = 'Property_type';
        go

2. Requirement: Sale or rent

Property can be marked as

  1. for sale or for rent, and

  2. has a process of not yet listed,

  3. then listed,

  4. then under offer,

  5. then sold or leased

iii. listing_type Table

DBML

  •         Table listing_type{
          id int pk
          description varchar
          Note: 'stores either for rent or for sale'
        }

SQL Server,

  •         create Table listing_type
            (
                id int,
                description varchar(20),
                --Note: 'stores either for rent or for sale'
                constraint pk_listing_type_id
                primary key (id)
            )

iv. listing(property)_status Table

DBML

  •         Table listing_status{
          id int pk
          description varchar
          Note: 'Stores the life cycle of property'
        }

SQL Server,

  •         create Table listing_status
            (
                id int,
                description varchar(20), 
                --Note: 'Stores the life cycle of property'
                constraint pk_listing_status_id
                primary key (id)
            )

3. Requirement: price

A property can have either a sale price or a monthly lease amount when it is listed.

4. Requirement: features

A property can have a range of features such as including a washing machine and dryer, garage, carport, front gate, and alarm system.

v. feature Table

DBML

  •         Table feature {
          id int pk
          feature_name varchar
          Note: 'Examples are washing machine, alarm, garage'
        }

SQL Server,

  •         create Table feature 
            (
                id int,
                feature_name varchar,
                --Note: 'Examples are washing machine, alarm, garage'
                constraint pk_feature_id
                primary key (id)
            )

vi. property_feature Table

DBML

  •         Table property_feature{
          property_id int
          feature_id int
        }

Adding Foreign key to property_id column of property_feature table

  • Ref: property.id < property_feature.property_id

Adding Foreign key to feature_id column of property_feature table

  • Ref: feature.id < property_feature.feature_id

SQL Server,

  •         create Table property_feature
            (
                property_id int,
                feature_id int,
                constraint fk_property_feature_property_id
                foreign key (property_id) references Property(id),
                constraint fk_property_feature_feature_id
                foreign key (feature_id) references feature(id)
            )

5. Requirement: process

Property can go through the process multiple times. such a getting sold and then leased.

vii. listing Table

DBML

  •         Table listing {
          id int pk
          property_id int
          listing_type_id int
          listing_status_id int
          price int [Note: 'stores either a sale price or a rental price']
          created_date date
        }

Adding Foreign key to listing_type_id column of listing table

  • Ref: listing.listing_type_id > listing_type.id

Adding Foreign key to id column of property_status table

  • Ref: listing.property_status_id > listing_status.id

Adding Foreign key to property_id column of listing table

  • Ref: listing.property_id > property.id

SQL Server,

  •         create Table listing
            (
                id int,
                property_id int,
                listing_type_id int,
                listing_status_id int,
                price int ,
                --[Note: 'stores either a sale price or a rental price']
                created_date date,
                constraint fk_listing_property_id
                foreign key (property_id) references property(id),
                constraint fk_listing_listing_type_id
                foreign key (listing_type_id) references listing_type(id),
                constraint fk_listing_listing_status_id
                foreign key (listing_status_id) references listing_status(id)
            )

6. Requirement: employees on a property

A property can have one or more employees working on the property being listed, in different roles such as Property manager for selling agents, over different time periods.

An employee can have different roles on different listings.

employee Table (more later)

  •         Table employee{
          id int pk
        }

viii. property_employee Table

DBML

  •         Table property_employee{
         property_id int
         employee_id int
         role_type_id int
         start_date date
         end_date date
        }

Adding Foreign key to property_id column of property_employee table

  • Ref: property_employee.property_id > property.id

Adding Foreign key to employee_id column of property_employee table

  • Ref: property_employee.employee_id > employee.id

Adding Foreign key to employee_id column of property_employee table

  • Ref: property_employee.role_type_id > role_type.id

SQL Server,

  •         create Table property_employee
            (
                property_id int,
                employee_id int,
                role_type_id int,
                start_date date,
                end_date date,
                constraint fk_property_employee_property_id
                foreign key (property_id) references property(id),
                constraint fk_property_employee_employee_id
                foreign key (employee_id) references employee(id),
                constraint fk_property_employee_role_type_id
                foreign key (role_type_id) references role_type(id)
            )

ix. role_type Table

DBML

  •         Table role_type{
          id int
          description varchar
          Note: 'Examples are Property Manager or Selling Agent'
        }

SQL Server,

  •         create Table role_type
            (
                id int,
                description varchar(20),
                --Note: 'Examples are Property Manager or Selling Agent'
                constraint pk_role_type_id
                primary key (id)
            )

7. Requirement: employee details

For Employees, we want to know their first and last names, the date they started, the date they left the company if they have left and their job title.

x. employee Table

DBML

  •         Table employee{
          id int pk
          first_name varchar
          last_name varchar
          start_date date
          end_date date
          job_title varchar
        }

SQL Server,

  •         create Table employee
            (
                id int,
                first_name varchar(20),
                last_name varchar(20),
                start_date date,
                end_date date,
                job_title varchar(20),
                constraint pk_employee_id
                primary key (id)
            )

8. Requirement: inspection

A property can have multiple inspections while it is for sale or for lease, each of which has a date and time and an employee.

xi. inspection Table

DBML

  •         Table inspection{
          id int pk
          property_id int
          inspection_datetime datetime
          responsible_employee_id int
        }

Adding Foreign key to property_id column of inspection table

  • Ref: inspection.property_id > property.id

Adding Foreign key to responsible_employee_id column of inspection table

  • Ref: inspection.responsible_employee_id > employee.id

SQL Server,

  •         create Table inspection
            (
                id int,
                property_id int,
                inspection_datetime datetime,
                responsible_employee_id int
                constraint pk_inspectione_id
                primary key (id),
                constraint fk_inspection_property_id
                foreign key (property_id) references property(id),
                constraint fk_inspection_responsible_employee_id
                foreign key (responsible_employee_id) references employee(id),
            )

9. Requirement: clients

We want to capture information about potential clients:

  • Their name

  • Email address

  • Phone number

  • Which properties they may be interested in

  • Which properties they have attended an inspection for

xii. client Table

DBML

  •         Table client{
          id int pk
          first_name varchar
          last_name varchar
          email_address varchar
          phone_number int
        }

A client can be interested in many properties and

A property can get interest from many clients.

So, it's a many-to-many relationships

SQL Server,

  •         create Table client
            (
                id int,
                first_name varchar(20),
                last_name varchar(20),
                email_address varchar(20),
                phone_number int,
                constraint pk_client_id
                primary key (id),
            )

xiii. client_property_interest Table

DBML

  •         Table client_property_interest{
          client_id int
          property_id int
        }

Adding Foreign key to client_id column of client_property_interest table

  • Ref: client_property_interest.client_id > client.id

Adding Foreign key to property_id column of client_property_interest table

  • Ref: client_property_interest.property_id > property.id

SQL Server,

  •         create Table client_property_interest
            (
                client_id int,
                property_id int,
                constraint fk_client_property_interest_client_id
                foreign key (client_id) references client(id),
                constraint fk_client_property_interest_property_id
                foreign key (property_id) references property(id),
            )

xiv. client_inspection Table

DBML

  •         Table client_inspection{
          client_id int
          inspection_id int
        }

A client can attend many inspections and an inspection can have many clients.

So, it's a many-to-many relationship.

Adding Foreign key to client_id column of client_inspection table

  • Ref: client_inspection.client_id > client.id

Adding Foreign key to inspection_id column of client_inspection table

  • Ref: client_inspection.inspection_id > inspection.id

SQL Server,

  •         create Table client_inspection
            (
                client_id int,
                inspection_id int,
                constraint fk_client_inspection_client_id
                foreign key (client_id) references client(id),
                constraint fk_client_inspection_inspection_id
                foreign key (inspection_id) references  inspection(id)
            )

10. Requirement: offers

We want to capture the properties that a client has applied for rent or made an offer to buy.

An offer can be made on a property that is currently listed for sale or for lease.

The offer can be accepted or rejected and multiple offers can be made on the same property and by the same person.

xv. offer Table

Creating a lookup table for offer,

DBML

  •         Table offer{
          id int pk
          client_id int
          property_id int
          offer_status_id int
          offer_amount int
        }

Adding Foreign key to offer_status_id column of offer table

  • Ref: offer.offer_status_id > offer_status.id

Adding Foreign key to client_id column of offer table

  • Ref: offer.client_id > client.id

Adding Foreign key to property_id column of offer table,

an offer is more related to the property.

  • Ref: offer.property_id > property.id

SQL Server,

  •         create Table offer
            (
                id int,
                client_id int,
                property_id int,
                offer_status_id int,
                offer_amount int,
                constraint pk_offer_id
                primary key (id),
                constraint fk_offer_client_id
                foreign key (client_id) references client(id),
                constraint fk_offer_property_id
                foreign key (property_id) references property(id),
                constraint fk_offer_offer_status_id
                foreign key (offer_status_id) references offer_status(id)
            )

xvi. offer_status Table

DBML

  •         Table offer_status{
          id int pk
          description varchar
          Note: 'Stores values such as Accepted, Rejected, In Review'
        }

SQL Server,

  •         create Table offer_status
            (
                id int,
                description varchar(20),
                --Note: 'Stores values such as Accepted, Rejected, In Review'
                constraint pk_offer_status_id
                primary key (id)
            )

11. Requirement: sale

When a property is sold or leased, we want to store the details of the contract and transaction, including:

  • The type of contract (sale or rent)

  • A link to the actual contract

  • The clients involved in the contract and their role

  • The employee involved in the contract

  • The date the contract is signed

  • The start and end date of the contract (used for rentals)

  • The status of the contract

xvii. contract Table

DBML

  •         Table contract{
          id int pk
          property_id int
          listing_type_id int
          contract_document varchar
          responsible_employee_id int
          client_id int
          contract_status_id int
          signed_date date
          start_date date
          end_date date
        }

Adding Foreign key to property_id column of contract table,

  • Ref: contract.property_id > property.id

Adding Foreign key to listing_type_id column of contract table,

  • Ref: contract.listing_type_id > listing.id

Adding Foreign key to responsible_employee_id column of contract table,

  • Ref: contract.responsible_employee_id > employee.id

Adding Foreign key to client_id column of contract table,

  • Ref: contract.client_id > client.id

Adding Foreign key to contract_status_id column of contract table,

  • Ref: contract.contract_status_id > contract_status.id

SQL Server,

  •         create Table contract
            (
                id int,
                property_id int,
                listing_type_id int,
                contract_document varchar(20),
                responsible_employee_id int,
                client_id int,
                contract_status_id int,
                signed_date date,
                start_date date,
                end_date date,
                constraint pk_contract_id
                primary key (id),
                constraint fk_contract_property_id
                foreign key (property_id) references property(id),
                constraint fk_contract_listing_type_id
                foreign key (listing_type_id) references listing_type(id),
                constraint fk_contract_responsible_employee_id
                foreign key (responsible_employee_id) references employee(id),
                constraint fk_contract_client_id
                foreign key (client_id) references client(id),
                constraint fk_contract_contract_status_id
                foreign key (contract_status_id) references contract_status(id)
            )

xviii. contract_status Table

DBML

  •         Table contract_status{
          id int pk
          description varchar
          Note: 'Examples such as Signed or In Progress'
        }

SQL Server,

  •         create Table contract_status
            (
                id int,
                description varchar(20),
                --Note: 'Examples such as Signed or In Progress'
                constraint pk_contract_status_id
                primary key (id)
            )

ERD Diagram

SQL Query File [Link]

A few Scenarios the database design couldn't handle,

  1. A client is of two people?

  2. Multiple inspections for an inspection or a contract?

  3. Interest - Inspection - Offer - Contract could be a process.

Conclusion

Learning Objectives,

  1. Database Design

  2. How to identify the attribute

  3. How to establish relationship between attributes

Source: ERD[Link], YT [Link]

Author: Dheeraj.y

Connect with me:

Did you find this article valuable?

Support dheerajy blog by becoming a sponsor. Any amount is appreciated!

Databasesdatabase designSQLsqldevEntity Relationship Diagrams