twzERD - online entity relationship diagrams Adding relationships | Database tables | Custom CSS

Overview

twzERD online is a tool for creating simple database entity relationship diagrams (ERD). It's suitable for small databases of less than about 20 tables (more with a higher screen resolution). twzERD doesn't generate an image file! You need to get a screen capture and crop to suit your needs. The final image is best saved as GIF or PNG format.

twzERD uses the HTML5 <canvas> element; it should work with Firefox, Safari, Opera, Chrome and recent versions of Internet Explorer.

    sample image

Background

This project was never really going to happen. It started off as a simple PHP script to get table and field names from a database, and display them in boxes on the screen. Five minutes later when that was done, I thought "wouldn't it be good if I could draw relationships between the tables?". No — too hard to do, and really, what's the point? Then while taking a coffee break, the expression "canvas element" popped into my head and wouldn't leave. Ok, so why not have a play around with it, just for fun? A few hours later I had the beginnings of twzERD, but still thinking it was just a little exercise for my own amusement and even if I finished it, would probably never use it again. As it progressed bits were tacked on with no overall vision, so it looks a bit committee-built. And it takes a bit of fiddling around to get a nice result, but it's fun to use! (or is it only fun for me?) Anyway, to keep a long story the same length, I got the thing working and then decided as I'd gone that far, I might as well put it online for you.

Usage

The tables can be dragged around by their title. Basically, you start by arranging the tables, then adding relationships between fields. Only the relationships are drawn on the canvas; the tables are just <div> elements floating around on top. It probably would have made more sense to draw everything on the canvas, but.. nah. To get a final image, do a screen capture and crop it in your favourite graphics app.

The canvas size is set to fill the entire window on page load. If you want a smaller canvas, reduce the window size and refresh the page. You can then maximise the window, and the smaller canvas size will remain.

Adding relationships

Relationship lines can be created between any two fields in different tables. Select the fields from the two fieldname dropdown lists, and the type of relationship for each (explained below).

Relationship lines are drawn between the two tables if there's room. If the tables are too close together, the line will extend from either the left or right of both tables. When you add each relationship, you can decide whether this will be left (L) or right (R). You can switch it later if you change your mind.

The type of relationship is indicated by the shape at each end of the line, as follows:

Type Shape Example from the tables image above
Many (required)
  == 1 or more
MR Each customer can have multiple bookings, and must have at least 1 booking.
Many (optional)
  == 0 or more
MO Each location can have multiple bookings, but might have no bookings.
One (required)
  == exactly 1
SR Each booking must be for exactly one customer.
One (optional)
  == 0 or 1
SO Each customer can have one subscription, or not.

Erase / Lock / Nudge

Relationship lines can be erased from the screen with the "Erase lines" button. You can stop the table boxes from being accidentally moved by ticking the "Lock tables" box. There are four "nudge" buttons for moving table boxes up, down, left or right. By first dragging out an area on the canvas with your mouse, you can restrict which lines are erased, and which boxes are nudged (boxes whose top left corner is inside the selection area).

Current relationships

Once you've added at least one relationship line, the "Current relationships" section will be visible. The "Redraw All" button will redraw all relationships (d'oh). You can also select any relationship from the dropdown box and either swap its L/R orientation, or remove the relationship completely.

Save/load relationships

You can save your current layout to the server, after first giving it a name. Any number of different layouts can be saved, and reloaded as required. This allows you to go back to an earlier layout in case you've made some changes you want to revert. The "Tables" button lets you define your own tables and field names (see next section).

Table definitions

By default, twzERD will read table definitions from a local database. To use custom tables and fields, start by clicking the "Tables" button to reveal the table definition box. You can manually define the tables and fields by entering table and field information directly in the table definition box, one table per line, in a format like this:

customer~CustId,FirstName,LastName,Email
sale~SaleId,SaleDate,CustId,TotalAmount
saleitem~Id,SaleId,ItemId
item~ItemId,ItemDesc,Price,InStock

Alternatively, to automatically create table definitions for an existing MySQL database, download erd-tabledef.php and run the script on your server, then copy the output and paste it into the table definition box. Information in addition to just fieldnames can also be included (primary key, nullable etc); for details of the format used, see the erd-tabledef.php download.

Once submitted, you will be redirected to a unique URL allowing you to add relationships to your table definitions.

Updating table definitions: Usually whenever you submit tabledefs, you will be assigned a new unique URL and there will be no relationships defined. However if you have already defined some relationships and want to resubmit your tabledefs (for example to remove an unwanted table or field), you can include the prior URL code ("name"). When you do this the table definitions will be replaced, but your old relationships will be preserved. Any custom CSS (see below) will also be preserved and won't need to be included again. Example:

customer~CustId,FirstName,LastName,Email
sale~SaleId,SaleDate,CustId,TotalAmount
saleitem~Id,SaleId,ItemId
item~ItemId,ItemDesc
name=47aa9fd30bc44a0aa82fa87ad12d9403

You could also exploit this feature to get a friendlier URL for updating your data in the future. If you specify a name the first time you submit your tabledefs, this name will be used instead of the 32-digit unique string. Be warned however, that if anyone else decides to use the same name, your data will be overwritten - choose a name wisely!

Custom CSS

You can also enter custom CSS into the table definition box to affect how the table divs will look. This is especially important if you have more than about a dozen tables. In order to fit them all on the page, you will likely need to reduce font sizes (depending on your screen resolution). You can reduce further by showing field names only (ie not extra field attributes).

Custom styles can be specified on multiple lines, but each line must start with css:. For example:

customer~CustId,FirstName,LastName,Email
sale~SaleId,SaleDate,CustId,TotalAmount
saleitem~Id,SaleId,ItemId
item~ItemId,ItemDesc
css: .dbtable { width:100px; background-color:#f9f9f9; }
css: .dbtable h3 { font-size:12px; }
css: .dbtable ul { padding-left:10px; }
css: .dbtable li { font-size:10px; }

Your CSS will only be accepted if it validates with the W3C CSS validator.

 


Return to twzERD online. For assistance or to add a comment, please visit the main tweezy utilities site.