Month: February 2021

Although I’m no database administrator, every-so-often I need to model out table relationships as part of some new project. Most folks would use a tool like Microsoft Visio to model their entity relationships. However, Visio costs money and I don’t have it…er, I don’t have Visio. (I don’t have much money, either, given the exorbitant costs of college these days.)

So, what’s a cheapskate like myself to do? Why, use free tools, that’s what! Lately, I’ve found some joy in the combination of SQLite and DBeaver.

Step 1: Code out your database

In a text editor like Notepad++, start coding out your database. Let’s take the canonical example of an orders database. You might rough out some of those tables with the following script:

DROP TABLE IF EXISTS items;
CREATE TABLE items(
	item_key VARCHAR(64) NOT NULL,
	item_desc VARCHAR(100),
	unit_price DECIMAL(5,2),
	CONSTRAINT item_pk PRIMARY KEY (item_key)
);

DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
	customer_key VARCHAR(64) NOT NULL,
	customer_name VARCHAR(100),
	CONSTRAINT customer_pk PRIMARY KEY (customer_key)
);

DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
	order_key VARCHAR(64) NOT NULL,
	customer_fk VARCHAR(64) NOT NULL,
	order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT order_pk PRIMARY KEY (order_key),
	CONSTRAINT customer_foreign_key FOREIGN KEY (customer_fk) REFERENCES customers(customer_key)
);

DROP TABLE IF EXISTS order_details;
CREATE TABLE order_details(
	order_fk VARCHAR(64) NOT NULL,
	item_fk VARCHAR(64) NOT NULL,
	quantity INTEGER,
	CONSTRAINT order_detail_pk PRIMARY KEY (order_fk, item_fk),
	CONSTRAINT order_foreign_key FOREIGN KEY (order_fk) REFERENCES orders(order_key),
	CONSTRAINT item_foreign_key FOREIGN KEY (item_fk) REFERENCES items(item_key)
);

Save your script to a file like orders_db.sql.

Step 2: Generate a SQLite database

Now, pipe your script into SQLite to generate your orders database. In Windows, you can open a command shell and run a command like the following:

C:\sqlite-tools-win32-x86-3300100\sqlite3.exe orders.db < orders_db.sql

Step 3: Open your database in DBeaver

DBeaver supports tens of different types of databases, including SQLite. Creating a connection to your newly created SQLite database is relatively easy. Follow the guide to creating a new connection. When it comes time to select your database type, you can click the “SQL” tab and scroll down until you find the selection for SQLite. All your connection needs is a path to the database file you created in Step 2–in my example, I called the database orders.db.

Step 4: Create an ER diagram from the tables in your database

DBeaver includes a cool feature called Entity Relation Diagrams where it allows you to easily visualize your database. In the application, you can select File > New from the menu and find the ER Diagram selection under the DBeaver folder. From there, you should be presented with a list of available databases including the one you setup in Step 2. Go to your orders database, find your tables, and select all of them. Once you name your ER Diagram, you should be able to click the Finish button. If all goes well, you should see a visual like the following:

Having a visual of your database helps you identify missing columns, bad or missing relationships, etc. You should now be able to identify these problems, go back to Step 1 with the code to fix them, and repeat the steps again until your model starts to look more appropriate. With my tables visualized in an ER diagram, I find it easier to share and discuss my ideas with teammates rather than trying to talk through the code. ER diagrams also make for good images in documentation.

Bonus: your ER Diagrams in Jupyter Notebooks

Recently, I was looking for more information on using markdown in Apache Zeppelin notebooks when I happened upon this discussion of pegdown parsers in Zeppelin. The article mentioned the YUML plugin. YUML seems to be a handy way of creating simple UML diagrams.

So, on my laptop, I opened up a new Jupyter notebook, created a new “markdown” cell, and wrote out the following HTML:

<img src="http://yuml.me/diagram/scruffy/class/[customers]<>1-*>[orders], [orders]<>1-*>[order_details], [items]<>1-*>[order_details], [note: My database model {bg:cornsilk}]" >

This code then rendered this UML diagram:

Certainly not as detailed as my SQLite/DBeaver solution, but quicker to achieve and still helpful with team discussions and documentation. In the comments, feel free to add your “poor person” approach to database modeling.