December 19, 2014

Building a CRE Database (Nerd Guide)

People throw data in databases all the time but if you don’t give it some advance thought, you might as well stick with Excel.  In fact, that’s the mistake many database novices make – thinking in terms of Excel and not understanding the relational aspects of databases.

Databases are made up of tables.  Each table contains records – a row – with data in columns – much like you see in an Excel spreadsheet.  But in a database, the tables are “linked” (related) to each other in ways that you define. And instead of one table with a gazillion columns, you get to define the tables in groups of logically related data.  Properties, for example, have enduring characteristic data – an address, APN/Tax ID, market area and so on.  Listings will inherit much of the same data, but also have data that uniquely describes the space.  Listings are also temporary and can eventually be converted to comparables.  And comparables will inherit most of the listing data and require additional information (actual price, buyer/tenant, etc…).

When creating your tables you need to keep in mind is something called normalization – the need to minimize redundancy within a table and dependency between tables.

For most CRE purposes, reducing redundancy, i.e., the same data repeated over and over again, isn’t too much of a problem.  What you do want to avoid are excess columns.  For a listing, for example, you could have one listing agent or who knows how many.  Instead of creating multiple columns for every possibility of number of listing agents you can create a separate table that holds every listing agent that’s linked to each listing.  You can also do the same for brokerage companies if it is typical in your area to have multiple agencies/agents in multiple offices representing an owner.

The other part of normalization is dependency.  When you add, edit or delete a record in one table, how does that affect another table?  For example, when you delete a listing, you wouldn’t want to delete the basic property information and have to enter it all over again, so properties and listings data are in separate tables.  Or, when you edit a property owner, you wouldn’t want to have to find all instances of that owner in the database.  Instead, you’d want to edit it in a Contacts table and create an update to all the other tables/records where that owner appears.

Hopefully, the diagram below will help you better visual a typical CRE database structure with tables added to reduce redundancy (Multiple_Agents and Prop_Mult_Addr).  Dependency reduction is achieved in part by creating separate Company and Contact tables.

Note that not all the data columns are listed for each table.  What is shown is the Primary Key (PK) for each table – basically an identifying number for each row/record in the table – and any Foreign Keys (FK).  Foreign Keys are what link one table to another.

CRE database diagram - CREOutsider
So what’s the big deal of putting the same data in a bunch of tables instead of one giant spreadsheet?  Well, you won’t have to sort through one giant spreadsheet any more.  Or copy and paste data into flyers, reports or presentations.  You can either use Excel to extract just the data you want or build a “front end” – search and results pages, reports….  Just like you can on LoopNet.

Admittedly, this is a simplified version of what has to be done to create a database from scratch.  You’ll still have to deal with issues like data formats, required fields, number of characters, adding more tables that hold list values for drop down selections and so on.

Maybe you can do it – or get someone else to do it for you.  Or…you can find an application where the tables and structure are already created for you and all you need to do is supply the data.  There’s only a handful of those out there –  REApplications (now owned by CoStar, so not sure what’s going on with them….) and Real Estate Assistant (REA).   (If anyone knows of any others, post a link in the comments!).  For the most part, you can only view or get data reports based on what the program offers, though REApplications does allow custom queries if you’re familiar with SQL.

Access or Filemaker – two desktop software standards (Filemaker has much friendlier online options plus iPad integration)  – or online “create your own databases” like Zoho or Intuit’s Quickbase are good from scratch options.  But you’ll still need an understanding of how all the pieces fit together to get the most out of doing it yourself.

But whatever route you take, you’re going to have to divide and organize your gazillion columns of data to fit into your or someone else’s database tables.  Since this post is pretty long already, next week I’ll post on how to prepare your data.

Comments

  1. Chase Pursley (@gcpursley) says:

    Chris, the database guides are great. Keep them coming!

    • Have a few left. Good to know someone is reading them! Thanks…

      • Chase Pursley (@gcpursley) says:

        Chris, you should consider doing a screencast to show people how to set up a basic comp database with MS Access. Not sure if that would eat in to your core business – but I’d pay for.

        • Why pay!? There are some very good YouTube videos on this. For importing Excel data into Access, check out this one:
          http://youtu.be/-aH56dxqLhg.

          For setting the relationships between tables, I really liked this one. Shows how you set them and enforce integrity between tables, i.e., a record cannot be created in one table without an existing record in another table (e.g, you need a property record/property code before you can add a listing…)
          http://youtu.be/gk8g031YRFQ

          These should explain the process for most (newer) versions of Access.

          For creating forms/reports, I saw quite a few videos. OF the ones I watched, this was good even though the guy mumbles a bit:
          http://youtu.be/huxHwvO6FHo

          My posts were directed more to the design of the database and prep work – both often overlooked. The other stuff isn’t that difficult but if you’ve got a bad design and messy data, you’re not going to get what you want out of your database.

  2. This is very timely and I’m looking forward to the follow up. We are developing tables and screens to meet our needs so the insight on planning and organizing our data is very helpful. Thanks!

Trackbacks

  1. […] You’ve got the relationships set, know what data you have and what you want.  Now comes the hard part.  Cleaning up your data and quantitatively creating relationships using ID numbers (those Primary Keys you saw in last week’s post). […]