A senior colleague 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 right, the business model itself is fairly straightforward, however when you are dealing with customers who are at the edge of society with chaotic and fragile lifestyles, things can be become very complex very quickly. This article is designed to give you a quick insight into why desiging and building systems to manage our services are not as straightword as you may first imagine. Although this is not a lesson in database design theory, it is important to understand some basic principles of what goes on ‘under the hood’. I’ll try to keep it brief so that we can continue on and understand the complexities.
Entities: In a database we try to model the real-world things we are interested in. these are typcially called Entities. Each Entity is a single occurance of an Entity Type. For example in a sales order system we would be interested in Customers, Orders, Products and Products on Orders Each entity is stored in a table (think of it like an Excel sheet). Each row represents one copy of the Entity and each column contains some information we need to know about that Entity.
Relationships: Entities rarely exist in isolation; they are intrinsicly linked together: Customers have Orders, Orders have Products. These relationships are managed by having a unique value (or combination of values) that link the tables together (think VLOOKUP() in Excel); so a Customer will have an ID, and the Order will have a column called CustomerID which allows us to get back to the Customer table. These relationships are called one-to-many: one Customer can have many Orders, one Order can have many Products . This is why they are known as Relational Database Management Systems, or RDBMS
3rd Normal Form: 1-to-many relationships are good; we can manage them easily. an Order has many Products, but a Product also has many Orders. this is a many-to-many relationship and they can cause us problems. For example we don’t want to store all the product information against each order, or all the order information against each product so we need a ‘bridge’ In this example it would be OrderLine. This holds the OrderID and ProductID (along with some other information which could be useful like order quantity). Orders->OrderLine is now 1-to-many and is Product->OrderLine. There is more to it, but this is the essential concept of 3rd Normal Form.
Now that we have the basics down, lets look at our housing system requirements. We have Assets and we have Tenants
|1||House||1994||1 Acacia Ave||100.00|
|2||Garage||2008||16A Smith St||10.00|
|1||Joe Smith||07888 888 888||01/04/1970||Male|
|2||Anne Jones||07999 999 999||01/12/1980||Female|
but how do know which Tenant is renting which Asset? we could store the TenantID on the Asset but an Asset will have many Tenants over time and we could only hold the current Tenant. We could store the AssetID on the Tenant but a Tenant could rent multiple Assets (house and garage), We need a bridge table: Lets call it a Tenancy
You can see that each Tenancy is linked to one Asset and to one Tenant, however things are more complicated than that. We may rent an Asset to more than one Tenant (joint tenants) but we don’t want two rows in the Tenancy table because there is extra data in there (StartDate/Tenancy Type) which we don’t want to duplicate. Rather than rent to a Tenant, we need to rent to a TenantGroup and assign the Tenants to the TenantGroup
A tenant can be part of more than one Group, and a Group can have more than one Tenant. A Tenancy agreement may also be for more than one Asset (e.g. House and Garage) so we need to have revise the Tenancy table and remove Asset, then have a new table TenancyAsset. Also a Tenancy can change types (e.g. Starter to Full), so we need to have TenancyEpisode to hold the type with it’s start and end dates. To model the basic relationship of who is renting which asset we already have six tables:
When you factor in different elements of the rent, contact details, bank accounts, periodic rent charges, arrears and anti-social case management, repairs processng and all the other activities that go on in a housing association it doesn’t take long to get sseveral hundred tables in the database.