In an earlier article, we created a simple relational database in Microsoft Access. However, that design has some flaws (which were by design, pardon the pun). In this article, we'll walk through the issues and make some changes to the table design to address those issues.

That being said, I make the assumption that the following concepts aren't alien to you:

• One-to-Many Relationship
• Many-to-Many Relationship
• Junction Table
• Primary Key
• Foreign Key

If they are, I suggest reading through my earlier article on the prior version of this database first.

I won't be spending too much time talking about forms and reports, since that may be too much detail for the purposes of this article. However, the sample database will include revised and improved forms and reports for you to look at. I do recommend taking time to look at those in design view and try to understand how they work.

Let's start by taking a look at the table design again.

One of the issues has to do with compilation CDs. In this design, we can't assign multiple artists to a CD. The workaround was to create a catch-all artist called 'Various'. The problem with that is that there is no way to figure out how many albums an artist has appeared on.

Another issue is that songs are associated with albums, but there's no association between songs and artist. So, even though we can see all the albums a song appears on, a song could end up being associated with more than one artist since songs are associated with albums, and albums are associated with artists; and for compilation CDs we used a pseudo-artist, i.e. 'Various'. For example, 'Do you sleep' by Lisa Loeb is associated with two artists - Lisa Loeb and Various. 

The same issue exists with genre. A song is associated the genres of the albums it's in rather than having it's own genre, e.g. 'Do you sleep' by Lisa Loeb is associated with two genres - Pop and Various.

There is another issue that is also of concern. In the table for songs, 'tblSongs', there is nothing associated with artist. Just the song title. And the only other table it's associated with is tblAlbum via the junction table.So what happens when there are two different songs by two different artists?, e.g.:

• Stay by Lisa Loeb
• Stay by BlackPink

Two very different songs, yes? (How about these: Hello, Hello, Hello)

What about a song that is associated with more than one artist?, e.g.

• Big Rock Candy Mountain by Lisa Loeb and Elizabeth Mitchell

Sure, you could create a 'Lisa Loeb and Elizabeth Mitchell' artist, but then a match on 'Lisa Loeb' wouldn't find this song since 'Lisa Loeb' <> 'Lisa Loeb and Elizabeth Mitchell'. Yes, you could do a search for artists names that contains 'Lisa Loeb', but what if there was someone named 'Mona Lisa Loeb'?

One way to solve the problem of multiple songs with the same name and the problem multiple artists associated with a song is to associate songs with artists. We create a many-to-many relationship between them.

To solve the issue songs being associated the genre of the album (or albums) it's associated with we will associate songs with genres instead, i.e. songs rather than albums will be associated with genres. So now we can list all the genres of songs in an album, i.e. an album can have multiple genres based on the songs in it, but a song can have a single genre.

What about songs that are multi-genre? The song Iine by Babymetal has elements of Metal, Pop, and others. I'm going to say that is it's own thing since parts of the song are metal, other parts are pop, some parts overlap, and so on. But as a whole, the song is it's own genre. Now, you could make a completely valid argument for creating a many-to-many relationship between Genre and Song to say that the song belongs to multiple genres. Both of these are valid choices, each with their own merits and consequences. There is where your subjective interpretation of what of the data structure makes a difference.

Now, I started off describing the issues of the original design using pseudo-artist, i.e. 'Various', for compilation CDs. And I haven't mentioned anything about that so far. Again, that is by design.

Now that songs are related to artists, and songs are still related to albums (via tblTrackListings), there is no reason to associate albums with artists. We can just show (i.e. via a query) all the genres associates with all the songs in the album. By fixing one thing, we've fixed another issue. Here is my revised table design.
[Note: I added SongDuration but didn't really fill it out. Left it in there to show what else you might store in that table]

This brings me to another point I want to drive home. When designing a database, you need to consider the design as a whole, and not only individual relationships between entities (i.e. Artist, Song, Album, Genres are each individual entities that have associations between them).

Which entities have only one relationship (e.g. Artist & Song) and which ones have multiple relationships (e.g. Song and Artist, Song and Album), is sometimes dictated by the data itself; other times it a little more subjective. This is where you need to look at what you are trying to do with the data, i.e. what queries, calculations, and reports you want to do with the data.

I've made the choice to say a song can only have a single genre, even if it has elements of multiple genres. I decide to add a genre called 'Multi-Genre'. If that sounds like it's just a fancier version of 'Various' pseudo-artist, it's because it is. But I decided a 'Various' artist is a problem, and that 'Multi-Genre' is singular genre because that is how I have decided to view a song like that BabyMetal song. It all boils down to how you view data.

Not everything will be as subjective though, e.g. a person can only be in one of two states - alive or dead. Of course the some people may disagree over the definition of dead (e.g. is a person with no brain activity but kept alive with machines considered alive?). In this case, the subjective part is a matter of when a person is considered dead, but not whether or not 'state of life" is a binary condition, i.e. alive or dead not somewhere in-between.

The two take-away points from this article are:

1. Take the time to carefully consider your table design.
1. Consider the what your initial table design means. What does it mean for the data you can enter and how you could use it? What can and can't you do with it? Then revise and refine. The table design determines everything else (forms, queries, reports), so any changes here can (and usually do) have a big impact on everything else. So if you start building everything THEN determine you need to change your table design, you may waste a lot of time rebuilding forms and so on.
2. There can be multiple valid approaches to your table design.
1. Not always the case, but it's good to try to see what different approaches could mean for the table design. Go ahead and try to view the data from multiple viewpoints to see what the various approaches could mean for your database.

Additional design considerations

Here are some things for you to try to think about how you might handle:

1. Do you want to separate the artists first and last name into two fields, e.g. FirstName & LastName?
1. What do you do about bands, e.g. U2?
2. What about artists with a middle name (Billy Ray Cyrus), or that use a single name (Sting), or stage name vs real name (Engelbert Humperdinck or Arnold George Dorsey)?
2. What about songs with multiple versions?
1. Radio Edit
2. Single Edit
3. Various remix versions
4. Different language versions
3. Do you need to worry about the unlikely scenario of two different songs with the same title and duration that belong to the same genre (or genres, if you decide to have a many-to-many relationship between song and genre).
4. What about artists with the same name?
1. Maybe add more info, e.g. Date of birth. But what would date of birth mean for a band? Make it Date of Birth/Band Formation?
5. For bands, do you want to include tables to store band members?
1. Do you store band role? Singer, drummer, and so on. What if someone has multiple roles?
2. What if band members change? What if they leave, then come back later on?
6. For songs, do you want to store information about:
1. The song writers - Music & Lyrics
2. Musicians who appeared on it?
3. Producer?
4. Studio recorded at?
7. What about songs that were re-recorded by the original artist?
8. What about cover songs?
9. What about albums with no title?
10. Do you want to store album cover images? What if there are multiple versions?
11. What if albums were released differently in different countries?
1. Different track order
2. Bonus tracks

Links

• Sample File