Ever wonder about the two sides of handling databases? On one side, there's the programming bit – creating databases in languages like SQL, sometimes abstracted away in an ORM (Object Relational Model) if you're using modern frameworks. On the flip side, there's the storage and utilization of the info stored in those databases. In this article, I'll explore the idea of adding a user interface (Admin panel) to the database to make it relatable even to non-programmers, while keeping it intuitive for the tech-savvy. I'll also propose a tool I've developed that I think, perfectly addresses this balance.

To recap, a database consists of tables, and these tables have rows and columns—in the context of relational databases, which is our focus in this article. One table is not a database. It becomes a database when there are several tables with relationships between the them. In the example below we have a students and schools table where a student belong to a school and a school has students.

If you were to use a generic tool like PHPMyAdmin (as shown below) to edit information in our example database, it might be a little intimidating if you're not a programmer. PHPMyAdmin comes with settings and various functions for data manipulation. However, the way it presents information likely depends on the nature of the data being represented. Given that the database described here contains only technical information, there isn't enough contextual data for PHPMyAdmin to present it in any other way.

We can also utilize WordPress Admin, which is less generic. Adding the two tables might be a bit tricky from a programmer's perspective, but the point I want to highlight is that we end up with a user-friendly UI compared to PHPMyAdmin. This is because WordPress provides a way to add a bit more information about the data stored in the database.

What I find interesting is that tools like PHPMyAdmin or perhaps Microsoft Access are often viewed as databases, while WordPress Admin is seen as a Content Management System (CMS). So, it seems that by adding just a bit more information about the data stored in the database, an admin panel transitions from being too programmer-centric to something that feels more familiar to most people.

Adding context to data stored in the database in a generic way and achieving a user-friendly UI/admin panel should be straightforward, right? Surprisingly, it appears to be a challenging problem. What I find interesting is that while there are standard database software options like MySQL, SQLite, Oracle, and a few others (less than ten), we haven't been able to come up with THE tool to create and represent information in a generic way.

Why is this the case? Well, we want two things. We want things to be made quickly, and we also want to have control over every little detail of how things should look. These two wishes are kind of opposite. We can't agree on where the balance between control and easy use should be. That's why we have many different tools, and that's why we haven't found a tool that everyone agrees on as the standard.

Introducing Quickmin

Quickmin is the name of the tool I developed that I think strikes a balance between control, ease of use, and development. The core idea behind Quickmin is to simplify the process of describing how a database should be set up. In traditional SQL, you'd use create table statements and more for this purpose. My goal with Quickmin is to handle all these technicalities, eliminating the need for users to manually engage with the database setup. I envisioned it as a tool that provides a singular, comprehensive database description—equipped with enough information to generically create a user interface.

To set up a project with Quickmin, refer to this [link].

Database Description Example: YAML Format

Here is an example of how you would describe the schools and students database. We are using YAML for this.

collections:
    students:
    fields:
        <Text id="name" fullwidth listable/>
        <DateTime id="enrolled" />
        <Real id="age"/>
        <Reference id="school" reference="schools"/>
schools:
    recordRepresentation: name
    fields:
        <Text id="name">
        <ReferenceMany id="students" />

In this YAML description, we have defined two collections or tables: "students" and "schools." Each collection has specified fields or columns. Pay attention to the <Text id="name" fullwidth listable/> within the "students" collection; it includes meta-information about how the field should be presented, such as being full width and listable.

The description also establishes a relationship between the "school" field in the "students" collection and the "schools" collection. This relationship signifies that a student belongs to a school, creating a "has-a" relationship. The <Reference id="school" reference="schools"/> tag is used to define this reference. A reverse relationship is also defined with <ReferenceMany id="students" />, indicating that a school has many students.

Additionally, when referencing records from another table (in this case, "schools"), we specify a recordRepresentation, which is the "name" field in this example. This ensures that when referencing records, the representation is based on the "name" attribute of the school, adding clarity and ease of understanding.

This is actually something interesting because we can define the database schema, for example, specifying whether the columns should be integers or text, which is very programmer-specific. On the other hand, we can describe the data that's going to be stored in the database from a UI (user interface) point of view. For instance, when we talk about the school, what about the school is most representative? The school name, right? That's not a database-specific detail; it's more of a user interface-specific aspect.

What's intriguing is that when all these aspects are considered together, the interface automatically becomes user-friendly. It's a delicate balance to manage – achieving a system that is both generic and user-friendly, catering to the needs of engineers and non-programmers alike.

Running Quickmin

Once we've described our database, the next step is to perform a quick migration with the command quickmin migrate. This command prints to the console the SQL it would execute and as well runs the migration process. Following that, a simple quickmin command provides us with a RESTful endpoint, enabling access to the database over HTTP.