Entity relationship modelling

From smultron.org

Jump to: navigation, search

Modelling can be a highly theoretical exercise. Instead, I'll be presenting an example. Here is a text which was provided by Pit Schulz, the project manager of the Wortwelle radio project. This text describes in a few simple sentences the basic workflow at this particular radio station.

In this example, I'll try to model the scheduler, as described in the chapter "The streaming system architecture".

Contents

Step 1: identifying entities and attributes

First, I have highlighted the words specifically used to describe the system.

Example:

Editors decide on a trimonthly program schema. Editor makes calendar entry according to program schema. Artist confirms (or updates) calendar entry (...) Add new event into calendar using show name. If needed add new profile for show for every calendar entry send a reminder / confirm replacement etc. if needed pre-produce show / stream / broadcast show (make photos, write down track list). Enter metadata, edit, cut show, encode it. Upload show (podcast), enter data, photos, text, track list into blog posting. publish podcast. repeat.

Step 2: classifying

In the table below I've listed the highlighted words. First I've done some cleaning up, identifying synonyms, sets and items foreign to the system. I'll be taking a top-down approach to model this workflow, so I'll be looking for entities and attributes. Entities are sets of data belonging together. Attributes are properties of entities. These properties can be as simple as a single number or can be entities themselves, but always belong to an entity.

ItemEntityNotes
Editor Editor Extends user, has create rights on schedule
Artist Artist Extends user, has edit rights on Emission
program schema Schedule
Calendar entry Emission Relationship between Schedule, Show and playlist. Emission is a bit of a technical term, but it is the correct name for this entity.
Calendar event Syn. for "Calendar entry"
Show Show
Show name Attribute of Show
Show profile Attribute of Show
Track Track
Track list Playlist Associated with emission
Metadata General term for descriptive attributes. Metadata can be attached to many entities, such as Shows, Emissions and Artists.
Posting Posting Similar to Emission, but in context of web integration. Not part of the system.
Podcast List of Postings, not part of the system

Step 3: simplifying

The next step would be to simplify the data. Some entities are not part of the system. In this example, these include entities which actually describe the podcast. While the podcast is an essential component for this particular radio station (see the case study Reboot.FM), they are not a part of the scheduler.

Another class of entities which can be ignored for modelling the scheduler are those entities which deal with authentication and authorization. If your goal is to program a scheduler yourself, you should build it upon a programming framework. This will allow you to concentrate on the specifics of the online broadcasting domain, while letting the framework take care of standard functionalities, such as authentication, authorization, validation etc.

The functionalities best left to the framework include creating and administering user accounts. To integrate your framework with the scheduler, you still need an entity, which I will call the contract. The contract is a relation between a user and a show.

This leaves us with a simplified table of entities and attributes:

ItemEntityNotes
program schema Schedule
Calendar entry Emission Relationship between Schedule, Show and playlist. Emission is a bit of a technical term, but it is the correct name for this entity.
Show Show
Show name Attribute of Show
Show profile Attribute of Show
Track Track
Track list Playlist Attribute of emission
Contract Contract Relation between a user and a show.

Step 4: Modelling the Entities

The previous iteration leaves us with 5 basic entities to describe the scheduler: Schedule, Show, Emission, Playlist and Track. Here's a schematic representation showing how these entities can interact with each other.

scheduler.png

There is one schedule, composed of several emissions. Each emission belongs to a show. A show in turn can have several emissions. A show is by contract presented by a user. An emission can have a playlist. A playlist is composed of several tracks.

If you have some experience building databases, you did probably notice that the relation between playlist and track is a many-to-many relation. Indeed, a playlist can have many tracks, and conversely a track can be part of many playlists. When using a relational database, this is typically implemented in an extra table called an association table.

So in a more suitable model, the many-to-many relation has been replaced by a table called "Cue". A track can be cued several times, and a playlist can have many cues.

scheduler2.png

Step 5: Adding attributes

Now that we have modelled the relationships between entities, we still have to add attributes to the corresponding entities.

To find out which attributes we need, we can take a look at a real life scheduler data model.

data_model.png

Please check Singers data model for the Reboot.FM scheduler (Singer, 2004), which you can find here: https://github.com/til/reboot-scheduler-datamodel]

A first scheduler prototype could be based on these entities, relations and attributes.

scheduler3.png

Personal tools