Data Modeling at your fingertips

(2021-July-30) A picture is worth a thousand words, the same way a visual database schema is better than a database model communicated by a multitude of data scripting text objects.

Someone may spend a significant amount of time trying to describe all the database tables' attributes, constraints and relationships between tables with words while a visual Entity Relationship Diagram (ERD) may only take a few minutes (or seconds) to tell the same story.

There are different and similar tools available to help you to create these visual ERD artifacts. The more visual appeal those tools may demonstrate, the fewer efforts would be required to add new information by keyboard-typing. Visual drag-drop experience will prevail, and only to type explicit list of attributes/types/etc. of your data model entities, that’s where your keyboard-typing skills will still be necessary.

DBdiagram

Recently I worked with this (https://dbdiagram.io/) ERD modelling tool, that not only reverses the order of your efforts to create such models but also reduces your struggles to visually define constraints and relationships. You simply write your data model code, and it renders the ERD perfectly. Strange, but I liked it :-). You can also export this keyboard-typing created model to other formatted documents.

I'm sharing their animated gif file to provide you with more visual and textual aspects of this very good tool.

As well as an example of how you can create your database model by just writing your code.

Table orders {
  id int [pk]
  user_id int [not null, unique]
  status varchar
  created_at varchar [note: "When order created"]
}
Table order_items {
  order_id int [ref: > orders.id]
  product_id int
  quantity int
}
// You can define relationship inline or separate
Ref: order_items.product_id > products.id
Table products {
  id int [pk]
  name varchar
  merchant_id int [not null]
  price int
  status varchar
  created_at varchar
}
Table users {
  id int [primary key]
  full_name varchar
  email varchar [unique]
  gender varchar
  date_of_birth varchar
  created_at varchar
  country_code int [ref: > countries.code]
}
Table merchants {
  id int [primary key]
  merchant_name varchar
  country_code int [ref: > countries.code]
  created_at varchar
  admin_id int
}
Table countries {
  code int [primary key]
  name varchar
  continent_name varchar
}
Ref: products.merchant_id > merchants.id
Ref: merchants.admin_id > users.id

DBdiagram uses DBML (database markup language), a markup language to define and document database schemas.

There are 3 types of relationships: one-to-one, one-to-many, and many-to-one

There are also 3 syntaxes to define relationships in DBML and I liked the Short form.

//Long form
Ref name_optional {
  table1.column1 < table2.column2
}
//Short form:
Ref name_optional: table1.column1 < table2.column2
// Inline form
Table posts {
    id integer
    user_id integer [ref: > users.id]
}

The free version of the DBdiagram tool allows creating up to 10 models, unlimited modelling along with versioning and password protection is available in the paid version of the product. Again, more technical details on how to use the DMBL language along with converting it to SQL can be found here: https://www.dbml.org/docs/.

DBeaver

DBeaver has a community-free community edition (https://dbeaver.io/) of its database tools, where along with a rich set of data engineering tasks that can be performed, you also have a very nice data modelling tool.

DBeaver supports a big number of database platforms: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, Teradata, Firebird, etc. But what I liked about this app, is a data modelling user interface. It looks more modern and visually engaging compared to other data modelling tools that cost a lot of $$$, but still resemble Windows desktop applications from the 90s.

One disadvantage with the DBeaver is that it only allows creating one layer of your model and there is no separation between logical and physical models, all data model changes are physical.

Oracle SQL Developer Data Modeler

Oracle SQL Developer Data Modeler is an interesting Java-based tool, developed by Oracle and targets its own database market, however, it can also connect to SQL Server. Reverse engineering of the data model is possible by connecting to a backend and pulling all your database objects' metadata, as well as forward engineering when you can apply and deploy your model changes to your destination database environment. This tool is also free - https://www.oracle.com/ca-en/database/technologies/appdev/datamodeler.html!

Logic and Physical modelling are also available which makes it a very good tool and places almost to the same rank as big and very expensive data modelling tools. You can model tables, columns, foreign keys as well as map logical data models to one or more relational models.

With the ERD modelling tool, you have automatic or manual options to layout your database objects, colouring schema is also configurable and multiple display visual options are available.

There is a very good set of informative documentation articles provided by Oracle for data professionals who want to learn more on how to use this tool: https://docs.oracle.com/database/sql-developer-data-modeler-18.1/DMDUG/data-modeler-concepts-usage.htm

Other very expensive data modelling tools

But both of these tools are very expensive, I feel that they will lose their market share toward less expensive data modelling tools even with fewer features but still stable and more agile to support the growing demand to design and share Entity Relationship Diagrams (ERD) with your peers.

I will stop talking about those expensive data modelling tools because they have their own marketing budgets to spend :- )