Recently, I attended a brown bag where one of our senior consultants, let’s call her Michelle, outlined the basics of conceptual and logical data models. I’m nowhere near Michelle’s level of technical expertise, but I still found her presentation to be both informative and intriguing. By the time it was over, I felt compelled to give myself an exercise in conceptual data modeling.

Since I’m not an analyst or a developer, I figured that it would help to have a system to model that I understood well. Fortunately, when I’m not toiling away in the consulting business I moonlight as a rock musician. I play in not one but two bands, both of which share some of the same members.

Here are a few things that I learned about conceptual data model when creating one for my band.

Three Components of Data Models

As Michelle’s presentation outlined, the first step in building a conceptual data model is to wrap one’s head around the three basic components:

Entities: What makes up a data model.

Relationships: How these entities relate to each other or don’t relate to each other.

Attributes: Data points associated with entities.

These are the high-level concepts that will be filled in as the model is developed to maturity. Putting it in slightly different language, think of entities as the nouns, and relationships as the verbs. Attributes are anything associated with an entity. Some people prefer to leave attributes out of the conceptual phase and define them later. However, since I already knew what a lot of my desired attributes were, I added them in as I went along.

Determining Entities – First Attempt

I began by scratching out entities in no particular order:

– Band

– Guitarist

– Singer

– Drummer

– Bassist

– Keyboardist

At this point, I realized I was already on the wrong track. For example, the entity “band” can exist without a guitarist, without a singer, etc. Also, it can exist with one person doing all of these jobs (e.g., Prince). So, were the instruments more likely to be attributes, as opposed to entities? What about a band member who plays more than one instrument?

Determining Entities – Second Attempt

For the purposes of my data model, I determined that the entity that I needed right below “band” was something else: “member.” However, “instrument” also needed to be its own entity to support the many-to-many relationship of band members to instruments. So, I made another attempt at my initial list of entities:

– Band

– Member

– Instrument

These worked better. Now, as with a lot of bands, we’ve had a few personnel changes over the years. We’ve even had members leave and come back. To handle this, I briefly considered making “active/former” an attribute of the “member” entity. However, I was worried that this would come back to haunt me, so I erred on the side of caution and made “status” its own entity with active/inactive as the value options.

Adding Entities

Since the purpose of a band is to create music, I decided that it would be appropriate to add some additional entities to represent this:

– Album

– Song

– Video

– Concert

– Tour

Considering Relationships

Things started to get sticky when I paused to think about the relationships. An album is a collection of songs, but a song can exist outside of an album as well. A tour is comprised of concerts, but not all concerts are part of a tour. I thought about the parent-child nature of these relationships, but I felt confident that these were all valid entities.

Also, I considered the overlapping members between my two bands, which represent many-to-many parent-child relationships between entities. Referring back to what Michelle had explained about data modeling, I felt reasonably sure that these relationships could get complex, but that the entities themselves were still valid.

Distinguishing Attributes from Entities

Next, I entered some gray territory. For example: is rehearsal space an entity or an attribute? In this case, both bands practice in Kyle’s garage, which makes our rehearsal space an attribute of a particular member. Transportation for both bands is provided courtesy of my trusty van, which is an attribute of me but feels like an entity when we need to get somewhere for a gig or when I need to fill the gas tank.

Considering this for a few minutes, I decided that the van and the rehearsal space were entities and not attributes. My reasoning was that these were physical things. Also, they were essential; these things needed to exist in one form or another for the band to exist because with no rehearsal space, it’s next to impossible to be a band, as with transportation. However, these would not necessarily always be tied to a particular member. So, I deemed them entities and not attributes. Honestly, I sweated this decision more than anything else in the whole process. Was I making a critical mistake in my data model? Only time would tell.

Borderline Entities

Thinking about the van and the rehearsal space led me to consider a lot of other borderline entities: the PA, lights, equipment, etc. Also, it led me to think about members who might not be full-time. For example, our friend Jon handles guitar tech duties at big shows but not all of the time. We have the same situation with Kris, our occasional live sound engineer. Were they member entities, or was there a separate kind of entity I should create for them? I decided to make an entity for “contributor” and put Jon and Kris there. I figured that I could define the relationships later in the modeling process to show whether these entities were essential or whether they were optional. (If Jon and Kris ever read this, no offense dudes!)

Mapping Entities and Attributes

Now, I had a pretty decent list of entities, and I could fill in their attributes, lay them out visually, and start mapping the relationships. Once again referring to Michelle’s overview, I saw that at the conceptual level these relationships don’t have to be fully attributed, nor do they have to be normalized. I could leave it somewhat loose for the time being and focus on getting the most important ones documented. With no modeling software at my disposal, I went to the next best available tool: a large white board. (Easy erasure provided an added benefit.) Here’s my scribbled list of entities and their attributes:

Piraeus Consulting - Table

 

On the white board, here’s what these relationships look like:

Piraeus Consulting - Relationship Mapping

 

Aside from my poor penmanship making many-many relationships look like little Blair Witch Project logos, this felt like a decent start to a conceptual data model. I decided to let it rest for a bit before proceeding to the next phase: building this into a logical data model. This will be a future blog post, so stay tuned. In the meantime, if you ever wondered if you could take a stab at your own exercise in conceptual data modeling, don’t be shy (unless you’re in three or four bands… then it might get complicated).

 

By Patrick Porter | Manager, Business Development