Our situation
Imagine following situation: You want to make an application that has a front- and backend. Now what the application needs to be able to do is simple: There are users, which can sign up. They can create shopping lists and in those shopping lists there are list items. Those list items can belong to one of three different categories, which all have different attributes: Food, which also has an expiry date, Toys, which have a minimum date and Office Equipment, which has a material. This may be a very simple example, but it makes inheritance so much more understandable. Now we have three different options to approach this: Make three different tables, STI and MTI. They all have advantages, but also downsides, which one can call inheritance tax (pun intended). We will quickly go through them so you know which one to pick in your next project.
Enjoy :)
Three different tables
Making three different tables is the first thought everyone has (except for Java devs, they love inheritance). That is also the simplest approach. The class diagram would look like following:
The benefits of this method are especially how simple it is to set up and the ease of filtering for one type. Where it starts to show its weaknesses is, when we want to do anything with all items. As soon as we only want to get them out of our database we have to query all three tables independently and apply changes to all three independently etc. This is a big issue, because fetching all out of the database will be the most used action.
What is STI
STI (Single Table Inheritance) is when all children inherit from the parent by making one big table. That would look as following in our diagram:
We can see that our parent table has three tables below it which all are connected with inheritance arrows. The big thing to remember is how our database saves that in the end. Databases don't actually make inheritance possible. What they do is put all the attributes onto the parent table and add another attribute called type, which defines what type that object is. In our case, if we had a food article minimum_age and material would be null. The database would save it like this:
Here we have the benefit of quick queries. Fetching all items is easy, and only fetching one means that we fetch all containing that one type.
The main disadvantage is that the table can get very big very quick. For every attribute we add to one table the Shopping List Item table gets bigger. This can get complicated quickly and when we want to send data we always need to parse it first, because it's inefficient to send this much data to the frontend every time.
What is MTI
MTI (Multi Table Inheritance) is when each subclass has its own table and is connected to the parent class. This solves our problem with having too many attributes on the same table and it also makes bulk actions very easy. Our downside here is, that we will need to query "2 layers deep" to get every single Item. The class diagram would look as follows:
Make a JSON field
What we can do in some databases is create a JSON field. That makes it able for our database to store schemaless data in our table. We would still need a type field, but instead of having a huge table with a lot of null attributes we would just have one table with two extra fields. The diagram would look like following:
There are two big downsides. A query that searches the json is not that efficient. Downside two, and the main killer here is, that this approach is not possible on every database system, especially if it is an old legacy database.
Conclusion
There is no correct way to do it, you always have to think about your own situation and what makes sense there.
Top comments (0)