Database Markup Language (DBML) is a Thing, and It’s Beautiful! (Find A Bird: Part 3)
With the bones of the project management software setup, I turned my attention to the database schema. I find databases fascinating.
Interviewers often ask “What do you think your first 30 days on a project would look like?” I usually explain how I would seek to understand the business environment and the needs of the customers. This is true, but the part I’m often most excited about is exploring the database structure. There is so much you can learn about the software by understanding the database, its tables and how they relate to one another. I don’t have the skill nor the time to look through the codebase to try and gain understanding about an application. Looking through the database is a great jumping-off point to start asking questions. For example, ask an engineer, “I noticed a column named XYZ; how are we using this information in the app”? Or, “This column and this column seem to be storing the same information, is this tech debt or are we using this for something else”? The answers to these types of questions are essential, but the more important learning happens through the conversation about the database schema and how pieces of information relate to one another.
OK enough about why databases are important. Lets talk about the database schema for Find A Bird and how I discovered DBML. I was looking for a way to document the database both with code and with visual diagrams. In my search I found dbdiagram.io. This amazing tool lets you type out your database schema in simple syntax, and as you type, it creates a visual diagram of your database. See the current state of the Find A Bird database schema at dbdocs.io/matt.birdnerd/find_a_bird.
You may be asking why I wouldn’t just write the code in SQL and be done with it?
Automated Visualization: One of the standout features of using dbdiagram.io is its automatic visualization capabilities. While learning DBML does involve a learning curve, the visual tools it provides make this effort worthwhile. Being able to see a graphical representation of your database schema as you define it is immensely helpful for understanding and communicating the database structure.
Ease of Reading and Writing: DBML has a readability advantage. In SQL, you typically create all your tables first and then use ALTER statements to define foreign key relationships. DBML, on the other hand, allows you to document these relationships directly within the table definitions. It also makes it easier to denote different types of relationships (like 1:1, 1:many, and many:many) which aren’t as immediately clear in raw SQL.
SQL Script Export Functionality: dbdiagram.io offers the convenience of exporting your DBML schema into SQL scripts tailored to your specific SQL flavor. This feature simplifies the process of setting up your database – just run the exported query in your database instance, and you're good to go.
Looking ahead, my next step is to implement version control for the database schema. Given that I expect the database to evolve significantly as the 'Find A Bird' project progresses, maintaining version history is crucial. While GitHub isn't particularly well-suited for database version tracking, I'm exploring the possibility of using Flyway, an open-source database versioning tool. This would allow me to effectively manage and track changes to the database schema over time."
