How to edit everything

When designing systems you come across a lot of interesting problems. These problems are pondered, considered and then go on to inform system design decisions. One of the oldest of interesting problems is, the what I like to call, ‘being able to edit everything’ problem. What do I mean by this?

Well there is a lot of business value in being able to edit information across a whole load of data. Lets take products as an example. Say a car maker switches a common component in their vehicles. Say the wheel bolt changes on all models. Well in the systems of that company someone would have to go through each vehicle and update the details of the wheel bolt to the new details.

Now the astute among us will go ‘Yes, but can’t each vehicle reference a piece of data about a bolt and then when things change all you need to do is change the piece of data that all the vehicles are pointing to?’ This solution is the one most people use.

But this problem can get more complicated when you have nested data.

What do I mean by that?

Well lets look at the case of electric sockets in houses.

Say this was how you organised the data in your company (when designing a database we call this a schema).

Seems reasonable.

Every location can have houses for of many different sizes.

But what if the materials used in the construction of the building also needed to be stored?

Well then you might do something like this.

So each location would have an number of entries for the materials used to build houses in that location.

The only problem with this is you might get lots of duplication. For instance; Birmingham might have brick houses and London might have brick houses. That means you would have two entries for ‘Brick’ in materials.

So to solve this we could layout our data like so.

Aha!

This looks better.

We can now have any number of locations and materials. This also solves two problems

1. If we had a 150m2 house, we could link it to brick houses in Birmingham and London.
2. If we need to define houses using any other type of data (i.e. type: residential or commercial). We can add that as an additional box going into the House box

Now a number of systems get to this stage. It is a good solution and works for recording common data across a number of data combinations. You can define a number of different house types (or setups) (I.e a 150m2 house with four rooms and a different amount of sockets for each room) and then say that these houses are available for the following location/material combinations.

It is useful for a company to have information in this format.

It is usually at this stage that the company says ‘That great! Now how do I change the number of rooms for 150m2 brick houses in Birmingham?’

Ugh! What?

Well if you want to be able to remove rooms from a brick house in Birmingham we could do…

So the ‘Room Remove’ box would record the location material combination and room you would like to remove.

Job Done!

Easy!

‘OK that’s great! Now how do I add a room to 150m2 stone houses in London?’

Oh.

Right.

Well we could do…

What happens if we want to add a room to a newly added size of house?

How about...

This works if we want to add a new room to a new house size, but doesn’t work if we want to add a new room to a pre-existing house size.

Ahhh.

How bout if we did?

Well then what happens if we need to add sockets to the new room in the Room Add box?

Hummm.

Well we could do…

No.

That doesn’t solve the problem. But it does give us an idea of adding a Only For field on the room box so that we can say ‘We only want this room for the following location material combination’.

Hummm.

Maybe we can do this…

Oooooo.

That looks better.

We can do the same for the Remove boxes as well.

Ahhhh.

There we go.

So. We can now have a common set of house type all with different sizes, rooms and sockets in each room. But if rooms don’t exist for 150m2 brick houses in Birmingham, we can remove them. And if 150m2 stone house in London have an extra room, we can add them. The great thing about this is we can also apply this to sockets in rooms, so a kitchen in Birmingham can have four more sockets then one in London for a common house type.

So all we have to do now is make this in a database.

And we’re done!