©Mike Moxcey 2001

Designing a Database: Brainstorming

If you had a user group, you would do this sort of brainstorming with them after explaining what entities are.  If you can't get a user group, you can also interview users individually.  Write down all the nouns and verbs they use.  The nouns will be candidate entities; the verb can describe processes or scenarios for using the data.  Here is an example of a brainstorm I'd do for a music database.

My Music Database

For my personal Music Database, I want to track information I use in my presentations about music that I give to elementary schools.  Some of the things I want to track are the songs with their words and histories and chords and keys I play them in, the authors, the history of different instruments, and which instruments I use for which song.  Now I analyze that statement and see that my candidate entities are:

Song, Words, History, Chords, Key, Instrument, Author.

Further analysis or questioning discovers that "which instruments I use for which song" is actually called an Arrangement--another candidate entitiy.

I can further analyze these entities and realize a Song has Words but they can be split into verses and choruses and that some song have alternate words and choruses, but I don’t care.

This is a key point.  What you have to keep in mind when looking at your data needs is what do you have the will and the means to collect?  I could keep the verses separated so I could print them out in any order or assign different authors to different sections of the song or keep track of alternate versions, but my current book of music I’ve been keeping has the words and if there are new words then the author is attached to those words so in my database his name will just fit in with the words.  If I didn’t care about Authors at all, I could drop them.  If I were interested in tracking how songs have changed through out history, then I would need to tie Author to Verse and then collect all the verses under a Song Title.  Any way is valid.  Design so you get the information you need in a way that’s easy to collect and manage.

I drop Words (and Chords, too).
We’ve already seen that History is applied to either a Song or an Author so I won’t have that as a separate entity but will instead incorporate it as an attribute under Song, Author (and Instrument, too).

The Key is what key I play and/or sing a song in.  However, it isn’t tied directly to the song because it will change depending on the instrument.  And sometimes the key doesn’t matter because I’m not singing and am just playing on an instrument such as harmonica or tinwhistle which has its own instrinsic key.  But I realize that Key is actually an attribute of Arrangment which goes between Instrument and Song.
Here is my list of entities so far:

Song w/ Name, History,
Author w/Name, History
Instrument w/Name, History
Arrangement w/Key

Making an E/RD

An Entity/Relationship Diagram (E/RD) is a schematic, a blueprint of your database.  Following are the basic steps in building one.

Entities are usually represented by boxes.  The boxes hold the name of the entity and also show the major attributes.

Here is my first picture of the entities in my music database:

GIF of 4 Entities

Next I draw the relationship lines and examine them for both Cardinality (one or many) and Optionality (required or not).
Examine each relationship in both directions.

Song to Author:

A Song MAY have an Author but my database doesn't say it MUST have one.
A Song could have MORE THAN ONE Author
Author to Song:
An Author MUST have at least one song--I'm not putting in Authors unless I put in Songs for him or her
An Author could write MORE THAN ONE Song.
There is no relationship between Author and Arrangement or Author and Instrument.  If I wanted to track more information about Authors, I suppose I could make a link tracking what instruments they played but 1) I don't care and 2) If I did, I'd just make an entry in the Author's History column.

The relationship between Song and Instrument goes through Arrangement.  An Arrangement is a specific way a song is performed with an Instrument.

Song to Arrangement

A Song MAY have an Arrangment but it isn't required.
A Song could have MORE THAN ONE arrangement (if I do it on different instruments or do version in deifferent styles: folk, swing, rock)
Arrangement to Song
An Arrangement MUST be for a Song.  It cannot exist without a song
Here's an answer that could create a new entity.  An Arrangement can include MORE THAN one song.  That would be a Medley.  Now I have to decide if I want to put Medley's in or not.  If I do, there are multiple ways to model them.  For now, I'll just say an arrangement is only for one song.
Arrangement to Instrument
An Arrangement MUST include an Instrument.  This wouldn't be the case if I did songs a capella.
An Arrangement could have MORE THAN ONE Instrument.  Sometimes I get out my harmonica holder.  I may also switch instruments in the middle of a song.  A hammered dulcimer is easy to switch to even with a banjo strapped on.
Instrument to Arrangement
An Instrument does not have to belong to any Arrangement.  I can have instruments without using them for specific songs.
An Instrument can definitely belong to MORE THAN ONE Arrangement.

Here is the first pass at drawing relationships onto my E/RD (Entity/Relationship Diagram).  Next we will condense the 2 lines between relationships into 1.

GIF of Entities with dual relationship lines


Relationship lines are always drawn as single lines.  There are other ways of drawing E/RDs that use multiple relationships lines and that allow relationships to have attributes.  Those end up being mapped the same way to tables and this way of drawing an E/RD has a better one-to-one mapping of tables and foreign keys.  The main problem is that you and the users you're having verify the architectural drawing have to really understand how to read the relationship lines.

Reading Relationship Lines

When you read a single relationship line, you read it both directions.

First start from one entity and look at whether the line is solid or dotted.
If the line entering (actually leaving) it is dotted, then that relationship is optional (read Each occurrence of Entity One MAY HAVE)
If the line entering the entity is solid, then that relationship is required (read Each occurrence of Entity One MUST HAVE)

Now follow the line to the other end and look at whether the line is singly-ended or has one of those tree branches on it.
If the end is single, then the cardinality of the relationship is One-to-One (finish reading ONE AND ONLY ONE occurrence of Entity Two)
If the end is branched, then the cardinality of the relationship is Many (finish reading ONE OR MORE occurrences of Entity Two)

Here is the diagram redrawn with single relationship lines.  Read each from both ends and see if you match what was entered above.

GIF of Named Relationships

Create Associative Entities

Now we've got to get rid of the Many-to-Many relationships and put an Associative Entity in-between.
There was an explanation of why in the previous section .  We will end up creating an Instrument_Arrangement entity and a Song_Author entity and changing the Many-to-Many relationships into two One-to-Many relationships.  If an associative entity exists, then the relationship from it to the other entities MUST exist.  The associative entity also "intercepts" the many-to-many end going to the relationships so each many-to-many relationship ends at the new associative entitiy.

Naming Relationships

After you create the requisite associative entities (or you can sometimes do it before), you need to come up with good relationship names.  This helps you to further define and understand exactly what the relationship is doing.  For example, an Arrangement is going to Shape a Song.  When I completely write the relationship, it will say:
An Arrangement must Shape One and Only One Song
In Oracle Designer, the reports always say "must be" or "may be" so I'd change the name to "shaping" in one direction and "shaped by" in the other.
An Arrangement must be Shaping One and Only One Song
A Song may be Shaped By One or More Arrangements
Naming relationships, like naming Entities, is an art.  You can merely use Characterizing or Describing, but the more descriptive a name is, the more useful it is for organizing your thoughts.  And that's what the design phase of anything is all about.  Often I'll name a relationship by ignoring the Associative entity and looking at how the two Base Entities are related.  Then I'll use some useless name for the relationship from the Associative Entity out.

Here is what could be the final E/RD for my Music Database:


Previous Home Index Next