If you don't already know about it, Dia is an open source diagram drawing application, similar to Microsoft's Visio. It makes it really simple to create all sorts of diagrams, including UML diagrams, flowcharts, and network diagrams. It also allows diagrams to be exported to a number of formats, including PNG images (which can easily be converted to GIF images using my handy PNG2GIF utility).

Using a third party tool tedia2sql, you can automatically generate SQL schema for a number of databases, including Oracle, MySQL, Postgres, based on your database diagram. In this post I'll describe exactly how.

The diagram

To create the database diagram we'll be using Dia's UML tools. Classes represent database tables, and associations between classes represent foreign key constrains.

In this post I'll go over a simple example database with just three tables: Film, Film_Actor and Actor.

To create our database diagram we're going to use Dia's UML shapes. When you first start Dia it defaults to displaying the "Assorted" set of shapes, so you'll need to change the drop down to "UML"

Then we can click on the UML class shape, which is at the top left.Once we've done that we should have a diagram that looks like the one below:

Double clicking on the class will bring up a properties window, which has several tabs. On the first "Class" tab we can set the name. For the first table that is "Film". On the "Attributes" tab we can enter the rows that this table will have. Setting the attribute visibility to protected signifies that it is a primary key.

After adding all three tables and attributes we end up with a diagram like the one below:

At this point we could already generate a database schema, but it wouldn't contain any foreign key contraints. If you're not interested in adding them then you can skip straight to the generation part.

Foreign key contraints must be modelled using the UML aggregation tool: The line with the white diamond on one end. Select that tool, and then click on one table and drag and drop onto another table. When you let go of the mouse button a link should be drawn between the two. Double clicking on that line brings up a properties dialog.

Enter the foreign key in one end, and the row to which the foreign key referrs. The link between the Film_Actors table and the Films table is shown above, where the link is from film_id to id.

After adding a similar link between the Film_Actors and Actor table we end up with our complete diagram:

Generating the SQL

Now that our database diagram is compete we can generate the SQL commands to create the database. The command to generate the code for MySQL InnoDB is:

tedia2sql -i diagram.dia -o schema.sql -t innodb -f

If we want to generate SQL for a different database (such as Oracle) then we just need to change the -t argument. The generated SQL contains lots of comments, but the main sections are shown below:

-- Film
create table Film (
  id                        int not null,
  title                     varchar,
  year                      int,
  constraint pk_Film primary key (id)
) type = InnoDB ;

-- Actor
create table Actor (
  id                        int not null,
  name                      varchar,
  dob                       date,
  constraint pk_Actor primary key (id)
) type = InnoDB ;

-- Film_Actors
create table Film_Actors (
  film_id                   int not null,
  actor_id                  int not null,
  constraint pk_Film_Actors primary key (film_id,actor_id)
) type = InnoDB ;

alter table Film_Actors add constraint film_Actors_fk_Film_id
  foreign key (film_id)
  references Film (id)  ;
alter table Film_Actors add constraint film_Actors_fk_Actor_id
  foreign key (actor_id)
  references Actor (id)  ;

Other Dia tools

In this post I described how easy it is to generate SQL from a Dia diagram using tedia2sql. There are lots of other great third party tools to automatically generate output based on your diagram. The official Dia links page lists many of them.