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.
One difference between what we do and more traditional solutions designers is that we are not interested in selling you an infrastructure that you don’t understand. Wherever possible we will use the technology you already have and are familiar with and therefore likely to have in-house support. If you already have have internal web applications written in PHP, we won’t try and introduce Node or .NET. If you already have Microsoft SQL we won’t build our solutions in Postgres, Mongo or MySql (unless you want us to!)
Although it is easier to write the code so that file locations and names are hard coded or in a definition block at the top of the program, these applications are designed to be written once and survive changes in the organisational IT rules. By putting values into a configuration file or table, you can make it easier for the internal support team to understand what is happening and how to copy or move the application or it’s files and the permissions it relies on. It also means that the values can be changed without having to edit the main application
Typically a lot of the applications we write are in desktop software like Access or Excel. By default the logic lives in the same file as the data, whether that be tables or spreadsheets. There are two problems with this: First these files can be copied and you end up with multiple copies of the same application. This means that when you find an error, you now have to fix it in multiple places and you might not know which version of the code is being run in each copy. Second, when you want to upgrade the application, you can’t just take the the development copy with the new code and overwrite the original, because the development copy will not have the most current data
When writing code in desktop applications, especially Excel, we can’t take anything for granted. Workshhets get renamed, columns get moved, data may not be there or in the wrong format. Compensate where you can and make sure that errors are handled gracefully whereever possible
SCC is a technique for ensuring that different versions of your code are stored for posterity; you can compare one version with another and see how the code has changed over time. If you are having issues, SCC is a critical component to allow you to go back to a previous version. One of the reasons we can be so competitive on price is that we operate a joint-ip policy. You get a full copy of the code that we write for you to do what you want with it within your organisation; if you modify the code and ‘break’ it then we can compare that to the version we gave you and/or the most recent version and identify where it is likely to have gone wrong.
We believe that any application of a decent size needs documentation for three different audiences: the WHAT for the users running the application, the HOW for the 2nd and 3rd line support teams and finally the WHY for the analysts and architects. All applications that are more than just an Excel workbook (and even some of those!) come with comprehensive documentation.