In martial arts, fundamentals are everything. The basic kicks, rolls and jumps are far more important that any fancy technique you will learn during the years of practice. When you improve your basic rolls even a little bit, all the body throws that depend on your ability to roll on the floor will improve tenfold.
Software engineering is almost like training martial arts. We should practice every day, and focus on improving our fundamentals instead of running for the next big shinny thing that we read on Hacker News. Of course, this is easier said that done. It is always easier to learn the basics of a new programming language, than to get better at designing classes and polymorphism.
The rest of this article describes one of the fields that I tried to improve recently — Inheritance design in relational databases.
Administrators
Let’s start with a simple problem. Imagine, that we have a web application and we store information about our users in a database table called users. We keep only the basic information about our users: their first name, last name, and a username that they use to log in to our app.
We, the developers, are also users in our application and we have the corresponding table rows in the users table. But we want to have greater permissions on our system than the rest of our users. We want to be admins — users that can manage the rest of the users, and even tweak the web application’s features.
This can be achieved easily. We can simply create a new database table called admins that will contain information about our admin users. But when we start to write out our SQL CREATE TABLE statements, something feels very odd. All the columns from the users table need to be created in the admins table too. This, of course, feels very wrong. We should take a different route.
Instead of creating an admins table, we can also add an existing column to our users table and use it to distinguish between regular users and administrators. We will call this column type, and will allow two values in its fields: regular for our regular users, and admin for our administrators.
Corresponding to these values we can construct three SQL queries.
To fetch all the users we can write:
SELECT * FROM users;
To fetch regular users, we can write:
SELECT * FROM users WHERE users.type = 'regular';
Finally, to fetch all the admins, we would write:
SELECT * FROM users WHERE users.type = 'admin';
Adding moderators
As our web application grows bigger, we find it increasingly difficult to manage the user interactions on our website. Our company decided that we should allow some of the outstanding users registered on our application to gain additional privileges. We shall call these users moderators.
Let’s add support for moderators in our database. It is easy. We just follow the design for our administrators and allow the moderator value in the type column.
The moderator lookup is simple and follows the example from our previous queries:
SELECT * FROM users WHERE users.type = 'moderator';
Banning users
One of the privileges that our moderators need is the ability to ban misbehaving users from our application. However, we also want to prevent overly eager moderators, that would ban too much of our users.
There is a simple solution. Every time a moderator bans another user, a counter will be increased. Let’s call this counter banned_users, and add it as a column to our users table. But wait, this is very strange. For a regular user, a banned_users column doesn’t make sense. They can’t ban other users.
Well, there is no nice solution to this issue if we want to store moderators in the same table as our regular users. We will simply need to accept this fact and save a default null or 0 as banned_users value for regular users.
Vehicles
I have talked so much about our little imaginary web application, but I haven’t introduced the main concern it solves. Our imaginary application sells vehicles. Cars, trucks, bicycles, ships, boats… basically everything that can move you or your cargo. Our database also needs to reflect this fact.
Let’s use the previous idea to implement a table for all our vehicles. First, we should list all the information about our vehicles. Price, color, weight and brand name are just some of the things we want to store. Also it would be nice to store some more specific data, for example, number of wheels on a truck.
Full article...
