A Data Model for Listing Apartments and Other Rental Units

Everyone planning to move to a new location looks for good accommodations. In the Internet era, the very first place they tend to look at sites dedicated to listing rental properties. According to a poll by one well-known agency, 76% of people looking to rent turn to the internet first; this figure rises to 88% for those aged 25–34.

Let’s dive into a full-fledged data model that supports such sites and their underlying features. Our model also enables site users to conveniently filter search results.

It has been widely reported that the financial crisis of 2007–2010 may have contributed to the rapid growth of online rental marketplaces. Consumers are more likely to consider renting instead of buying in times of financial hardship. There are many online portals that assist people in finding their dream home. Thanks to these sites, someone sitting thousands miles away can search for and rent an apartment before they set foot in their destination.

Let’s dive into the features leveraged by such online portals. Basic features that a rental site must have include:

• Allowing rental owners or managers to post information about their residential units.
• Capturing any details unit owners provide about their properties. This should go right down to the granular level.
• Enabling renters to search for active posts matching their requirements.
• Narrowing down search results based on filter criteria like location, number of rooms, type of furnishing, in-unit amenities, the accessibility of public transport, and distances to nearest ATM, hospital, etc.

Considering the above requirements, I propose the data model as shown below as a template for an online rental portal.

I’ve divided this data model into three subject areas:

• Unit details – This has a generic name so it can represent any type of residential, unit including apartments, condos, penthouses, villas, etc.
• Parent Unit details – This covers information about the main building or campus where the unit is located.
• Furnishing details – In rentals, furnishings are totally different than amenities. Furnishings (desk, bed, sofa) are almost always inside a unit. Amenities (onsite laundry, meeting spaces, pools) are usually in a separate building. This subject area covers what furnishing items are provided to tenants as part of their lease. Here, I propose to log these details in a hierarchical way.

The Unit Details Section

First, consider the unit table. This table stores all the must-have details about a residential unit. For example, think of the unit type, number of bedrooms and bathrooms, carpeted area, price, etc. Most of the columns in this table are self-explanatory, with a few exceptions:

• Unit_heading – This column stores the post’s title text. Users are advised to keep titles crisp and clear. For example: 2 BHK Apartment in Manhattan – $3500/month.
• Unit_type_id – This signifies the type of residential unit: apartment, condo, villa, etc. Another table, unit_type, holds the various unit types.
• Leasing_info_id – This table contains all units’ leasing-related details. Leasing details for units in same building can be identical; that is the reason behind creating a separate table for capturing leasing information. I will explain this table later in this article.
• Posted_by – This column records the ID of the user who posted the item. I have included user_type in the user table so that we can include which type of user — i.e. owner, broker, leasing agency — did the posting.
• Is_active – This is a flag column that signifies whether a post is still active.
• Unit_number – This is actually an address column that records the unit number.
• Unit_floor_number – This column is applicable only in the case of apartments or flats, and it denotes the floor on which the unit is located.
• Parent_unit_id – This column signifies which building or campus the unit belongs to. Since there can be multiple units to be rented out in a building, I’ve create another table to hold building details.

Next, look at the unit_feature table. This table is primarily used to store all features and amenities associated with a unit. (Let’s use ‘apartment’ for ‘unit’ and ‘building’ for ‘parent unit’ from here on for clarity). This table contains following columns:

• Unit_id – This column refers to the unit table, and will act as the primary key for this table. It will also ensure there is one and only one record for each apartment.
• Furnishing_type_id – This column signifies whether an apartment is furnished, semi-furnished, or unfurnished. However, it does not clearly define the extent that a semi-furnished or furnished apartment is actually equipped. This is a real problem, but we’ll put it aside for now. I’ll go into more detail and present a solution later in this article.
• Is_air_considitioning and is_ceilling_fan_cooling – These columns show which type(s) of cooling equipment are provided with an apartment. There can be either or both of them, so I have made two different indicator-based columns for them.
• Num_of_assigned_car_parking – An apartment can have zero or more assigned parking spaces. Thus we have this column, which defines the number of parking spaces specifically assigned to an apartment. This column would store a ZERO by default.
• Has_carpet and has_hardwood_flooring – Both of these columns store the type(s) of flooring in an apartment.
• Is_central_heating and has_in_unit_fireplace – These features become necessities in areas where the temperature drops below freezing (0C/ 32F).
• Has_in_unit_garden, has_in_unit_laundry and has_walkin_closet – These three columns define if an apartment has these amenities.
• Are_pets_allowed – This column shows whether or not pets are allowed in an apartment. If a no-pet restriction is imposed for an entire campus or a building, this value would be ‘N’ for all units associated with the building or campus.

All these feature columns should be considered when designing the UI search control, and users should be allowed to narrow search results as per their expectations.

Now, we’ll quickly move on to the unit_image table, which stores images associated with an apartment. This is an important factor for many online renters. There can be multiple images uploaded for an apartment. We can restrict the maximum number of images allowed for an apartment by creating a check constraint on count(unit_id) or by using a trigger.

The leasing_info table is the last of this section. Its purpose is to hold information related to apartment leases. This can be the same for multiple apartments in a building, so it’s best to have a separate table to keep this information. This arrangement will also benefit the real estate agencies that manage the rental of hundreds or even thousands of units or properties.

The columns in leasing_info are:

• Id – This is the primary key for this table; it also serves as a foreign key in the unit table.
• Leasing_type – Possible values for this column include ‘short-term’, ‘long-term’ or ‘either of them’. There are some areas where leasing to students is explicitly described in the leasing document, and regulations accordingly differ. To meet that case, another item, namely ‘student_lease’, is added to the list.
• Is_sub_leasing_allowed – There are some areas, especially in central London, where rents are quite high. Therefore, people are allowed to sublease (rent to another person) their apartment if they have to move before their lease is up or if they will be gone for an extended length of time.
• Admin_fee – This column is applicable when there is any admin fee that to be paid by tenants.
• Brokerage_fee – This column will hold a value only if the post is published by a real-estate agent or agency.
• Security_deposit – This column stores the amount that tenants are assessed security deposit. Often, it is multiple of the monthly rent price.
• Rent_for_short_term_leasing and rent_for_long_term_leasing – These columns signify the length of time an apartment can be rented. Many times, the longer leasing tenure is, the lower monthly rental fees are. If you want to define ‘short-term’ in the data model, you can add another column to define a boundary between short and long terms. For example, any term less than six months would be considered short. In that case, the added column would store a value of ‘6’ to define this boundary.
• Is_lease_termination_allowed – Clauses detailing the early termination of a lease are usually written into the leasing document. When people look for a rental, this is another factor they look for.
• Lease_termination_amount – This column holds the monetary amount due (if there is one) in case the lease is broken by the tenant.

Subject Area# 2 – Parent Unit Details

There are only three tables in this section. The parent_unit table stores the building’s address and its amenities and features. The noteworthy columns for this table are:

• Id – Primary key for the table, and is referred in unit table as foreign key.
• Parent_unit_name – This stores the building or campus name.
• Total_floors – This column is applicable only for a building; it signifies the total number of floors it has.
• Number_of_units – This holds the number of apartments in a building.
• Number_of_elevators – This stores the total number of elevators available in a building.
• Street_name, city_name, state_name, country_name and zip_code – These are address columns and store the relevant building’s address details.

All the remaining columns are indicator columns for various amenities (swimming pool, fitness center, etc.) available in a building or campus.

Like the unit_image table, the parent_unit_image table stores images of a building and its amenities. Many people alo user outdoor areas as part of their selection criteria, so this table can store those images as well.

Everyone wants an apartment that’s conveniently close to ATMs, grocery stores, shops, or bus stops. The parent_unit_accessibility table stores the distances from these places to a building. This information would benefit those who have rather specific needs. For an example, someone who commutes to work via by subway will look for an apartment close to a subway station. Many rental sites have started listing this information along with the regular apartment details.

Subject Area# 3 – Furnishing Details

Usually we see three categories of furnishing: fully furnished, semi-furnished, and unfurnished, but these terms are never used systematically. What may be considered furnished in one area is very different from what would pass in another. A few rental sites allow rental agents or owners some ‘free space’ that they can use to define exactly what their apartment contains. But not all do. This is unfortunate, because this information is very important to potential renters; as it stands, they may only get it if they contact the owner or agent or visit the apartment.

Let’s use some sample items to demonstrate what an apartment might come furnished with:

• Kitchen: electric hob (stove), fridge, electric kettle, washing machine, and microwave
• Bedroom: double bed, TV, TV stand, set top box, wardrobe, and two chairs
• Bathroom: two wooden shelves and one curtain
• Drawing room (living room): Wifi router, one tea table (coffee table), one couch, two bean bags, and a vacuum cleaner
• Dining room: one table and eight chairs
• Garden (Yard or Lawn) – five wooden chairs and one table

So, let’s look at the tables we would use to hold records of all this stuff. Items in the first two tables would be preloaded into the search area,, and users could select or de-select items as they needed.

• Furnishing_category – This table holds various categories or areas where the individual items belong, such as the kitchen, bedroom, or bathroom.
• Furnishing_item – This table holds a complete list of furnishing items with their category mapping.
• Unit_furnishing – This table stores details of what items are provided in an apartment as part of its furnishings. I have added another column, number_of_items, to keep track of multiples for a particular item (i.e. 5 chairs).

Working with This Data Model

Suppose you’re looking for a three-bedroom apartment with a fitness center in-building and a school nearby (within a radius of 2 miles) in Wroclaw, Poland (postal (zip) code 51-649). The code would look like this:

SELECT u.* 
FROM unit u, parent_unit pu, parent_unit_accessibility pca
WHERE u.parent_unit_id = pu.id
	AND pu.id = pca.parent_unit_id
	AND pu.zip_code = ‘51-649’
	AND pu.has_fitness_center = ‘Y’
	AND u.number_of_bedroom = 3
	AND u.unit_type_id  = (SELECT id FROM unit_type WHERE unit_type = ‘APARTMENT’)
	AND pca.school <= 2;

Now imagine you’re searching for a studio apartment in Berlin (postal (zip) code: 03570) with at least one image tagged:

SELECT u.* 
FROM unit u, parent_unit pu, unit_image ui
WHERE u.parent_unit_id = pu.id
	AND u.id = ui.unit_id  -- INNER JOIN
	AND pu.zip_code = ‘03570’
	AND u.number_of_bedroom = 0
	AND u.unit_type_id  = (SELECT id FROM unit_type WHERE unit_type = ‘APARTMENT’);

Or perhaps you’d prefer a two-bedroom London row house, with a garden, that’s within mile of a Tube station:

SELECT u.* 
FROM unit u, parent_unit pu, parent_unit_accessibility pca
WHERE u.parent_unit_id = pu.id
	AND pu.id = pca.parent_unit_id
	AND pu.city_name = ‘LONDON’
	AND u.has_in_unit_garden = ‘Y’
	AND u.number_of_bedroom = 2
	AND u.unit_type_id  = (SELECT id FROM unit_type WHERE unit_type = ‘ROW HOUSE’)
	AND pca.subway_station <= 1;

Other Possibilities for a Rental Data Model

There are various countries, especially in central Europe, where the vacation home concept is widely accepted. For those of you not familiar with it, this is when people rent an apartment for a day or longer rather than checking into a hotel. Do you think our existing model supports vacation homes? What additional changes would be required to meet such a requirement?

Should we also build a way for individual users to like or dislike a post in their search results? In this case, posts could be displayed or hidden on subsequent searches based on how the user reacted.

Please let us know your views!