In-memory database design interview

Mar 24, 2024

What you can learn from this article?

I will show you the solution of the problem given often to the candidates on design interview. My solution will be realized on Java language, it will be much simplified solution in compare to real and at the end of this article you will find the link to Gitlab repo where you can find the source code of the design.

Problem to solve: an in-memory database design based on object-oriented principles.

Row class

Thus, let's find out what kind of classes do we need to create. Starting from the smallest element - we need to have Row class. Object of this class will represent one data record. We need somehow to get to the record, so Row will have the id property as unique identifier, information about creation time and update time are useful, so properties createdAt and updatedAt appears. Personally I prefer to store timestamp as Long (long integer) data type instead of Date, because it's much faster to compare numbers than dates. Next property is keyValueMap - the HashMap for storing record data in key - value way.

In the next step we will add getters and setters as well as some logic to the Row constructor. Also, we are going to implement setColumnValue() and getColumnValue() methods to be able to get or update separate column. It will be very good for us on the design interview if we will show our understanding of exceptions(find my another post about checked and unchecked exceptions) and implement NoColumnException.
The NoColumnException code:

in-memory database design - NoColumnException class

This is how Row class code looks like:

In-memory database design Row class part 1 — In-memory database design - Row class

Row class part 2 - IntelliJ — Row class code - column value and key value methods

Table class

The table consist from rows, that's why my next class will be Table class. The table has its name and its is going to be one of parameters in my Table class along with createdAt and updatedAt(same role as in Row class).

Good moment for implementing CRUD operations logic in methods: insertRow(), selectRowById(), updateRowById(), deleteRowById(). Also, COUNT operation will be represented by countRowsByKey() and countRowsByValue() methods and, of cource, we should think about transactions, - so methods startTransaction(), commitTransaction() and rollbackTransaction() are going to be realized.

Table class code on screens:

In-memory database design Table class part 1 — In-memory database design - Table class

Table class part 2 - IntelliJ — Table class code - insert(), select(), update() methods

Table class part 3 - IntelliJ — Table class code - delete(), countRowsByKey() methods

Table class part 4 - IntelliJ — Table class code - countRowsByValue(), startTransaction() methods

Table class part 5 - IntelliJ — Table class code - commitTransaction(), rollbackTransaction() methods

Database class

Time to implement Database class. Database has its name and this parameter will also be an unique identifier of the Database objects. Parameters createdAt and updatedAt has same role as in Row and Table classes. Parameter tables has HashMap data type and will store Table objects.

We need to implement methods createTable(), removeTable(), getTable(), getTables() - methods are needed to operate or list tables.

Database class code here:

In-memory database design Database class part 1 — In-memory database design - Database class

Database class part 2 - IntelliJ — Database class code - getters and setters methods

You, probably, noticed that Database implements Serializable. I'm implementing simple mechanism of storing databases to hard drive and restoring databases from hard drive just to prevent data loss(for example in case of power outage). Serialization will be used to implement that simple functionality of in-memory database.

DatabaseManager class

DatabaseManager - key class where all the things we wrote before should be gathered together to work. In this class databases parameter has HashMap data type and stores databases. Methods createDatabase(), deleteDatabase(), useDatabase() are dedicated to manipulate with databases.

In-memory database requires functionality that prevents from data loss. As I mentioned before - serialization will be used in this case. Methods saveDBState() and restoreDBState() are dedicated for that functionality.

DatabaseManager class code here:

In-memory database design DatabaseManager class part 1 — In-memory database design - DatabaseManager class

DatabaseManager class part 2 - IntelliJ — DatabaseManager class code - saveDBState() and restoreDBState() methods

Where you can find source code from this article?

You can find source code from this article on my Gitlab repo, just follow link below:

https://gitlab.com/birkof89/designs-java

What about tests?

Correct, test are integral part of software development process. However, I will be doing tests along with discussing the design source code on the video I prepared on my YouTube. You can find the link to video at the bottom of this article.