Schemaball

Circular visualization of database schemas

▲ SCHEMABALL CAN ACCOMODATE VARIOUS SCHEMA SIZES | (left) A Medline citation database with 12 tables. (middle) A ugene database with 35 tables. (right) A massive sequencing LIMS database with 135 tables.

Schemaball was published in SysAdmin Magazine (Krzywinski, M. Schemaball: A New Spin on Database Visualization (2004) Sysadmin Magazine Vol 13 Issue 08). Who cites Schemaball?

▲ A MYSQL SCHEMA OF OUR SEQUENCING LIMS DATABASE. | The schema ball shown here highlights tables which are linked to the Equipment table by a foreign key chain. The image on the left is the schema of the database in 2004. The database has grown significantly since then, and on the right is its state in late 2009. This image was generated with Schemaball and a little bit of post-processing.

Schemaball is an SQL database schema viewer. It requires Perl and a few modules, such as GD and optionally Math::Bezier and SQL::Translator. Schemaball creates flexible visualizations of database schemas. Schemas may be read from an SQL schema dump, flat file or live database.

what is schemaball?

Schemaball is a flexible schema visualizer for SQL databases. The purpose of Schemaball is to help visualize the relationships between tables. Tables are related by foreign keys, which are fields which store the value of a record field from another table. Foreign keys create a lookup relationship between two tables. Large schemas can have hundreds of tables and table relationships. Keeping track of them call can be tedious, error-prone and slow down the schema development process. Schemaball provides a means to create flexible, static graphic images of a schema. Tables and table links can be hidden, highlighted and foreign key relationships can be traversed forward or backward to highlight connected tables.

▲ A MYSQL SCHEMA OF OUR SEQUENCING LIMS DATABASE. | The schema ball shown here highlights tables which are linked to the Equipment table by a foreign key chain. The image on the left is the schema of the database in 2004. The database has grown significantly since then, and on the right is its state in late 2009. This image was generated with Schemaball and a little bit of post-processing.

Schemaball produces images called schema balls. Schema balls are schema visualizations in which tables are ordered along a circle with table relationships drawn as curves or straight lines. Using an input configuration file, all elements of the schema ball can be configured.

Schemaball is free software, licensed under GPL. It is written in Perl and requires a few CPAN modules to run. It's simple to use, while being able to produce high quality schema balls suitable for publication. In particular, you can use Schemaball to generate elements of a schema ball and then create a composite in an image editing program. One such result is shown in the figure at right.

A Need for Flexible Database Visualization

▲ A trivial schema with two tables: a PERSON table and a PERSONALITY table. Each person has a personality and different personalities are stored in the PERSONALITY table. Each person record has a lookup field for the primary key of the PERSONALITY table. The PERSON table therefore points to the PERSONALITY table and the PERSONALITY table is referenced by the PERSON table.

A database schema can be thought of as a directed graph. The nodes in the graph are the tables and the edges are relationships between them. The edges are created by foreign key fields in tables (see figure at right). The reason why the personality is stored in a separate table has to do with optimizing the way data is stored. There may be millions of records in the PERSON table, but only hundreds of different personalities. If the personality name is very long, the size of the database would be unnecessarily large since this same field value would be found in thousands of PERSON records. Storing a personality name in a separate table as a single record and associating it with a unique identifier, while other tables refer to it, is called normalization.

While in this example it's hard to get lost in the single table link, even with 10 tables keeping things straight can get messy. Schemaball helps to keep your head wrapped around your tables' connections. You can print out a schema ball and mark it up while you work on your schema. When I'm working on a database, I use the excellent mysqlfront to create tables and edit table fields. When I have my tables created, I print out a schema ball and mark up how I'd like to link the tables together. Once I create the foreign key fields, I can print out another schema ball and keep it for record.

Since it is designed primarily to Schemaball does not show the interal details of the tables. In future versions, support for display of fields may be included, to generate schema superballs.

Schemaball is at end of life

I am not longer actively working on Schemaball. As a prototype system, it works well to quickly figure out the extent of connectedness between tables and create some visually appealing images.

I've since been using the circular composition form in my Circos project, which applies this layout to visualizing genomes (Circos is particularly popular in the field of comparative genomes, in which relationships between two or more genomes are analyzed and displayed).

Case Study — Ensembl

Ensembl balls! See the structure of each Ensembl database, as drawn with Schemaball.

using Schemaball

To run Schemaball, you need Perl and a few CPAN modules. Schemaball has not been tested on Windows.