A Real Estate Agency Data Model

Other than location, what’s it take to run a successful real estate business? We examine a data model to help real estate agencies stay organized.

Buying, selling, and renting apartments or houses is really big business today. Most people are happy to pay a fee and let a professional real estate agency do the work for them. On the other hand, the company could act in its own behalf, buying properties to resell or rent. A real estate company may also lease a property then rent or sublease it and make a profit on the difference.

Obviously, keeping track of properties is an important part of running a real estate business. At the same time, dates are equally important. (e.g. When is a rental apartment going to become available? When is a piece of property going to go on the market?) In this article, we’ll take a look at a data model that can help real estate companies stay organized.

Real Estate FAQs

Before we start describing the model and its expected data, we’ll first answer some questions specific to a real estate business. Real estate has many terms and a full explanation of its jargon and principles go well beyond the scope of this article, so we’ll answer only the most common and basic questions here.

1. What can be considered an estate or a property?

   When we think of real estate, the first image we get is often of a house or some other dwelling. Real estate is much more than that. Buildings, offices, land, mineral resources and corps also fall in this category. For the purpose of this article, I’ll treat everything that is “unmovable” as real estate. Having said that, we’ll focus mainly on apartment buildings and houses.

2. Where is the estate or property located?

   For houses, buildings, and apartments this is very simple. We’ll know the exact address where the property is located. Land doesn’t have an address, but its position is defined by a land registry.

3. What data do we need to store?

   In our model, we need to store all the estates (i.e. real properties) and clients we work with. We need this information to create reports and also to improve our business.

   We can expect that we’ll communicate frequently with clients, so we must store all their contact details. We will also want to know which employee contacted the client and what interest the client expressed during the conversation.

   For properties, we need their details and current status at our fingertips so we can answer potential customers’ inquiries quickly.

   We’ll also store our contact history and any contracts related to either clients or properties.

4. How important are dates?

   Dates are always crucial, but I want to emphasize that they are especially important in real estate. We need to know the exact amount of time one of our rental properties is occupied so we can rent it again as soon as it becomes available. There cannot be any overlapping when two clients rent the same property. If a potential client expresses a desire to rent at some specific future date, we should store that information and get a reminder when that date is approaching.

5. What should our application look like?

   For this purpose, a web application is the best solution. Much of real estate work is office-based, but sales agents should be able to insert new data wherever they are. The most important functionality in our app is a fast search that can find clients, properties, and property statuses.

The Data Model

Our real estate data model consists of three main subject areas:

• Estates and locations
• Clients and contacts
• Contracts and transactions

There is one table, employee, that is outside of any subject area.

Please note that the employee and the estate tables in the Clients and contacts subject area and the client table in the Contracts and transactions subject area are just copies used to simplify the model.

We’ll take a look at the employee table first, continue with Estates and locations, move to Clients and contacts, and then finish up with Contracts and transactions.

The Employee Table

We’ll start with the employee table. It’s simple: it stores only the first_name and last_name of each employee. We could add other details like the employee’s tax ID number, their birth date, address, job role, etc. However, in this model we won’t be focusing on the employees, so all that we need is a way to associate employees with actions (like being assigned to a task or contract). This table will also let us record which employee participated in each client contact.

Section 1: Estates and Locations

The Estates and location subject area contains six tables that describe all estates (properties) we work with, their locations, and their current status.

The central table in this subject area is the estate table. It contains a list of all the estates we are, were, or will be working with. This includes estates for which we mediate between two clients, those that we own, any we’ve sold or rented to clients, and any we’ve leased or bought from clients. It also keeps a record of estates that we plan (or had planned) to do business with.

Since we’re focusing mainly on apartments and houses in this article, the attributes in this table are mostly related to them. If we would like to describe other types of real property, we could add additional nullable descriptive attributes. We could also simply enter those values in the estate_description attribute. The attributes in the estate table are:

• estate_name – The estate’s name. This could be our internal name for a property (“Stoker house”) or a well-known public name (“Bran Castle”).
• city_id – The ID of the city where the estate is located.
• estate_type_id – References the estate_type dictionary.
• floor_space and balconies_space – The size (in square meters) of apartment floors and balconies.
• number_of_balconies, number_of_bedrooms, number_of_garages and number_of_parking_spaces – Integer values for each category. Self-explanatory.
• pets_allowed – A Boolean value denoting if pets are allowed. This is mostly used for rental properties.
• estate_description – A detailed description of an estate. This is where we store any additional information, e.g. property history.
• estate_status_id – If an estate is currently available or not. We will use this field in our search function.

We’ve already mentioned two dictionaries that the estate table refers to, estate_type and estate_status. Both these dictionaries contain only an ID and a UNIQUE name attribute.

In the estate_type dictionary, we’ll store values like “apartment”, “house”, “field”, etc. The estate_status table will have values stating if the property is currently available or not, such as “estate leased”, “estate bought”, “estate sold”, “estate rented”.

We will define each estate’s location, not only by description (the estate. estate_description attribute), but also by its country and city. For this purpose, we’ll use two dictionary tables: country and city. Each country is uniquely defined by a country_name, which will be the only attribute (other than ID) stored in the table. On the other hand, each city has a name and a country. Some cities could have the same name, but we’ll assume that each city’s name is unique to its country – only one Vienna, Austria or Geneva, Switzerland. However, if we want to protect against duplicates, we could add a region attribute. For now, though, we will leave everything as-is. The city_name – country_id pair is the UNIQUE key of the city table.

The last table in this subject area is the in_charge table. We can expect that each estate will have at least one employee assigned to handle matters relating to it. This employee is responsible for things like communicating with clients, showing the estate to potential clients, and other administrative and legal tasks. In the in_charge table, we’ll have:

• estate_id and employee_id – Foreign keys that refer to the related estate and client, respectively.
• date_from and date_to – The interval when the employee was assigned to that estate. Notice that “date_to” can be NULL because an employee could take care of an estate indefinitely. When we assign an employee to an estate, we should make sure they are not already assigned to another estate by checking for overlapping date intervals. On the other hand, we can assign many employees to the same estate at the same time. This would be desirable when employees have different roles, e.g. one employee takes care of client communication, another employee shows that estate, another handles sales and legal contracts, etc.

Section 2: Clients and Contacts

The Client and contacts subject area consists of only two tables, the client table and the contact table. The two other tables shown in this area, employee:Clients and contacts and estate:Clients and contacts are just copies.

The client table contains records of all the clients we have ever worked with, including current and potential clients. Who is a potential client? It could be somebody who has said they want to sell, buy, or rent some property from us in the future. We need to store such clients’ contact details and properties for future use. The attributes in the client table are:

• client_name – For an individual, this field holds their first and last name. If the client is a legal entity, it holds the company or entity name.
• client_address – A text description of the client’s location.
• contact_person – First and last name (and probably a job title if the client is a business) of our contact person.
• phone, mobile and mail – The client’s contact details.
• client_details – All other details related to that client. These are stored in an unstructured text format.

The last five attributes in this table are nullable because they are not crucial. We’ll probably need to store information for at least one contact person, but we may not know in advance who our contact will be.

The second and last table in this subject area is the contact table. Here we’ll store data about every interaction we’ve had with clients. We’ll use this information to optimize our future business – for example, if a client asked to rent a certain estate from us when it becomes available, we should store that request and inform them when the estate is ready. The attributes in the table are:

• client_id – The ID of the client involved.
• employee_id – The ID of the employee involved in that contact instance. This can be NULL because a client may not contact any individual employee – e.g. maybe the client sent an email to the company account. Still, in most cases we can expect that we’ll know which employee handled an interaction.
• estate_id – The ID of the related estate. This is useful when the client asks for a certain property or if the client wants to sell or lease something we already have in our system.
• contact_time – The time when the contact took place.
• contact_details – Any unstructured notes we want to save about that contact. We might write something like “Client expressed desire to buy a house in neighborhood.”

Section 3: Contracts and Transactions

The last subject area in our model is Contracts and transactions. We’ll use it to relate estates with clients.

The central table of this section is the contract table. It is where we’ll store all contract details and relate contracts with clients and employees. The attributes in this table are:

• client_id – The ID of the client who signed the related contract.
• employee_id – The ID of the employee who signed the contract on behalf of our company.
• contract_type_id – References the contract_type dictionary and denotes if the contract relates to buying, selling, leasing, or renting property.
• contract_details – A detailed description of the contact, stored in text format.
• payment_frequency_id – References the payment_frequency dictionary and defines the intervals when invoices should be sent.
• number_of_invoices – The number of invoices that should be generated. If the company pays only once, a value of “1” is stored in this attribute and the entire payment_amount will be equal to the invoice_amount.
• payment_amount – The total amount paid.
• fee_percentage – The percentage we charge the client. For example, we might charge 5% of a house’s sale price as a fee. The value in this column should be the same as the contract_type.fee_percentage attribute for this contract. The fee_percentage attribute will be used to calculate the fee_amount when we enter a value in the payment_amount attribute.
• fee_amount – The total fee amount we’ll charge the client for this contract.
• date_signed – The date when the contract was signed.
• start_date – The date when the contract becomes valid (e.g. for a rental or lease contract).
• end_date – The date when the contract expires. It can be NULL in case we sign a contract that has no end date. However, in most cases we’ll know the end_date in advance.
• transaction_id –References the transaction table if the contract is a part of a transaction between two clients. It can contain NULL values because there won’t be a related transaction record if the contract is directly between us and a client.

The under_contract table relates contracts and estates. Beside the primary key attribute id, it contains only two foreign keys, estate_id and contract_id. This foreign key pair also forms the UNIQUE key of the table.

We’ll store records of every invoice we’ve generated in the invoice table. If the client makes a single payment for the whole contract, there will be only one record in this table for that contract. The same applies if we make a single payment to a client. If the client (or our company) chooses to pay in installments, there the same number of records as the value in the contract.number_of_invoices field. The attributes in this table are:

• contract_id – The ID of the related contract.
• invoice_number – A unique internal identifier for the invoice.
• issued_by – A text description of the invoice issuer. When we issue an invoice, we’ll store our company details here. If the client issues it, then their details will be stored here.
• issued_to – The opposite of issued_by. If we charge the client, then this attribute will contain their details; if the client charges us, then our details are stored here.
• invoice_details – All invoice item details.
• invoice_amount – The amount due on this invoice.
• date_created – The actual date when the invoice was created in our system.
• billing_date – The date when the invoice should be paid.
• date_paid – The actual date when the invoice was paid. It can be NULL until the invoice is paid.

We’ll use two more dictionaries to describe contracts, contract_type and payment_frequency. The contract_type_name field is used to denote the action we’re performing in the contract: “mediation (buying)”, “mediation (selling)”, “mediation (renting)”, “mediation (leasing)”, “buying (from a customer)”, “selling (to a customer)”, ”leasing (from a customer)” and “renting (to a customer)”. The payment_frequency_name attribute simply describes how often invoices will be generated, either by us or the client. It can store values like ”once”, “once per month”, “once every 2 months” and “once per year”.

If our company buys or leases some property, we’ll pay the client. This means we’ll be the one in the invoice.issued_to field and we’ll have to pay invoices. If we sell or rent an estate, the client will pay us and we’ll be the one in the invoice.issued_by field.

If we mediate a deal between two clients, we will charge a fee for our services. In this case, we’ll sign two separate contracts, one with the selling/renting client and another with the buyer/renter client. We’ll relate these two contracts together by assigning the same transaction_id to both. The transaction table is used to store records of deals we’ve mediated. The attributes in this table are:

• transaction_id – A unique ID for each transaction.
• transaction_type_id – References the transaction_type dictionary.
• client_offered – References the client table and denotes who is selling or renting an estate.
• client_requested – References the client table and denotes who is buying or leasing an estate.
• transaction_date – The date when the transaction will actually happen.
• transaction_details – All details related to that transaction, stored in an unstructured text format.

The final table in our model is the transaction_type dictionary. Values stored in this table are assigned to each transaction according to what it is: “buying/selling” or “renting/leasing”.

Running a real estate company is very complicated, demanding, and even risky. In order to keep everything working smoothly, a great deal of organization is needed. I hope that this data model helped you realize the complexity of this field.

As always, there are many ways to improve this model. Feel free to share your suggestions and comments.