Entity relationship diagram

From ClassDBI

Here's an example of an entity relationship diagram (or ERD). If you need to post sample Class::DBI code, this ERD would be a good one to use.

 +------------------+
 |  artistgroup     |
 |------------------|
 |  bandartistid    |
 |  personartistid  |
 +------------------+        +--------------+
     \|/      \|/            | band         |
      |        |             |--------------|
      |        |             | artistid     |
 +------------------+        | creationdate |
 |  artist          |--------| enddate      |
 |------------------|        +--------------+
 |  artistid        |        
 |  name            |        +-----------+
 |  popularity      |--------| person    |
 +------------------+        |-----------|
          |                  | artistid  |
          |                  | gender    |
          |                  | haircolor |
         /|\                 | birthdate |
 +-----------------+         +-----------+
 |  cd             |
 |-----------------|
 |  cdid           |
 |  artistid       |
 |  title          |
 |  publishdate    |
 +-----------------+
          |
          |
         /|\
 +-----------------+
 |  track          |
 |-----------------|
 |  cdid           |
 |  songid         |
 |  sequence       |
 +-----------------+
         \|/
          |
          |
    +----------+
    |  song    |
    |----------|
    |  songid  |
    |  name    |
    +----------+

For those unfamiliar with ERDs, ERDs are a graphical representation showing tables, columns within those tables, and the relationships between tables.

The boxes are tables. So in this example we have 7 tables: ARTIST, CD, TRACK, SONG, PERSON, BAND, and ARTISTGROUP. Within each table we list the columns in that table. For example, the ARTIST table contains 3 columns: ARTISTID, NAME, PARENTARTISTID. For consistency sake, the primary key (i.e. the column(s) that uniquely identify a row of data), are the first column listed. In the ARTIST table the ARTISTID is the primary key.

Furthermore, the ERD depicts the relationships between tables. The "crows feet" (i.e. the line between tables with the 3 pronged end), indicates the quantity, of the relationship. So, reading down, "an artist has many !CDs" and reading up, "a CD has a artist". The orientation (i.e. up, down, left, right) of the relationship is unimportant. It's the "crows feet" that's important. That is, the crows feet are on the side of the relationship which contains "many".

The Data Definition Language (DDL) for creating the above tables and relationships is included below:

• Oracle DDL for Example ERD

If you want to create nice images (JPEG, PNG, GIF) of your relations by simply providing your create table statements, check out SQL::Translator. It can also generate Class::DBI definitions from these statements.

--Gabriel

See also DB Visual Architect.