A senior manager at a Housing Association once asked me
We build houses and charge people to live in them, why do we have to make it so complicated?
In one
respect they are correct, it doesn't seem overly complex when you first look at it but as you get into the
nuances of the system it can quickly become very complicated, and indeed it needs to, so that we can ensure that
we can best cater for both our system users and ultimately our customers. To understand how it gets complex we
need to do a little bit of a primer on how a relational database works. Don't panic! we are not going to be
teaching degree level stuff here, just giving some context. Think of the database as a massive spreadsheet with
lots of tabs. Each Tab represents a collection of Things. A Thing is something we care about as a business
concept: Perhaps a property, or a tenant, or a repair
In order to keep track of these 'things' correctly we need to make sure the database is using something called third normal form. If you want more details I am sure you can research it in your favourite search engine; but for our purposes we need to do the following:
For example:
if Thing is a Tenancy then the unique identifier would be the Tenancy Number
if Thing was a Service Van, then Registration Number would not be a good identifier as the van could be replaced with one on the same registration number or have it's registration changed.
If Thing was a Repair Job that requires two operatives then we can't have two rows for the job, one for each operative; we would have to find another way to model the operatives on a job
If Thing was a Customer Contact, we would not want to store the number of times we have had contact with that customer as this would not relate directly to THIS contact
If Thing was a Customer we would not want to store their works and mobile phone numbers in the same field; each should be stored separately, otherwise we have to carry out a calculation on the field every time we want to extract the mobile number.
Starting at the very begining: We rent out properties to people so we need two tables: Assets and People. We normally name the tables in the singular so Asset and Person.
We need a good way to uniquely identify a property. Address is too long and PostCode is not unique. One option is the UPRN (Universal Property Reference Number) issued by Ordnance Survey, however this is long and it might be better to just use a unique incrementing number (and store the UPRN as a field on the Asset record). We will call this Asset ID
We also need a way to identify a person. Name is not good for several reasons: It may not be unique, it can be changed, it can be very long. In this instance we are going to use another incrementing number. We will call this Person ID
Seeing as we rent an asset to a person, we could record the Person ID on the asset or the Asset ID on the person. However the asset will get rented to a different people over time and a person may rent more than one asset so this solution has some serious limitations. Ideally we need a way to model the rental arrangement over time, and this will be a Tenancy. But we don't always rent to just one person, sometimes it may be a couple. We have already established that one of our cardinal rules is we can't hold two Person IDs in the same field so we need a new way to model having multiple people on the Tenancy agreement. We need a Person Group. We will attach the Person Group to the Tenancy and attach one or more People to the Person Group with a 5th table: Person Group Person
The Tenancy needs a unique reference Tenancy ID and will also hold a link to the Asset ID and Person Group ID as well as some useful information such as the start date, end date and rental amount (we will revisit this shortly)
This is what is known as a parent table and enables us to link multiple PersonIDs together. These will be represented by Person Group ID and will be recorded against the Tenancy.
A person can belong to more than one group so we will create a new bridging table with Person Group Person ID that links the PersonID to the PersonGroupID
So far, so good, but we one have one Rent figure against the Tenancy; this may actually be made up of several different charges (Rent, Property Maintenance, Warden fees, Personal Care etc.) Not every tenancy will have all of these charges therefore we need a new table to hold the charges and we may also want to group these charges together. Rather than storing the Rent total on the Tenancy, we need a new table to list the charges that are applicable to the tenancy at any given time.
The charge needs a unique code Charge Code and will also hold details such as
This is used to link similar charges together for reporting purposes Charge Group ID
We need a unique identifier Tenancy Charge ID
For the Person, we also need to hold information about how to contact them. They may have multiple phone numbers and email addresses.
Record Identifier Contact Method ID
In summary, just to model Who is renting what and how to get hold of them we need 9 tables.
Even this set up has some issues and limitiations and your real HMS will likely have in excess of 20 tables to model this correctly, but this is enough to give you an appreciation of what is required.
Now add functionality to capture and track customer interactions, follow up actions, repair requests, contractor costing, job completion progress, time tracking, ASB case management, tracking charging cycles, rent charges and receipts from customers, DWP and Councils, matching payments to charges, interfacing to Finance, HR and job allocation systems, you can see how quickly we can grow to several hundred, if not thousands of tables in the system; each one creating another opportunity for DQ issues and mismatched data.