Re: Some design issues?

Stu,

You create an index in order to improve the performance of a query. Think about how this data will be accessed by the end user and if the combination of "number of rows in the table(s)", hardware and DBMS will provide the results in a "reasonable" timeframe. The major downsides of creating indexes are:
1) The cost of updating an index when you insert or update an indexed row, and
2) The cost of storage.
You either anticipate a performance problem and create an index to solve it, or you hear complaints about how slow the system is, and create an index to solve it. By the way, I don't mean to imply creating an index is the only way to solve a performance problem, but it is one of the most common. Think about the where clause of the most typical queries. These are possibly the columns that need indexes.

Another observation that I may get flamed for... but here goes... What was your reason for using surrogate keys (ie the ID column in every entity)? In an ER model, I always try to use the natural key for entities. In my opinion, the only reasons for using a surrogate key is: 1) If the natural key becomes cumbersome (ie too many long columns), or 2) You are doing dimensional modeling.
I have too often seen the use of surrogate keys to overcome a lack of understanding of the data being modeled. "Hmmmm, I'm not sure what the natural key is, so let's make a surrogate key".

The other thing I noticed was ArtistID in the Song entity. I'm not sure it belongs there. As you stated, any song could have 1 or more artists (and any artist could have 1 or more songs). You resolved this M:M relationship with the Artist_Song entity. You also may want to think about allowing multiple genres.

My last two paragraphs raise an interesting question. If you remove the surrogate key, ArtistID, AlbumID, and possibly GenreID, the only thing left is the Title. Is title enough to make a song unique? Are there two songs out there that have the exact same title, but are in fact different songs? Not just sung by a different artist, or released on a different album by the same artist, or played in a different style (genre), but actually a different song? My guess is yes although I can't think of any off-hand. When I think about the Song entity, I think about the universe of Songs. When I hear a song in my head, I am not hearing "Song", but rather an artists interpretation of "Song". Song is the intellectual property created by an author. Perhaps the unique key to Song would be title, and author. This gets a little tricky when you consider songs that are written by collaboration. Think of Elton John songs. The lyrics are written by Bernie Taupin, and the music is written by Elton John. But, if I call the author Elton John, or I call the author Bernie Taupin, we are still uniquely identifying the song and know what song we're talking about.

Having said all that, it seems to me the Song entity is not the place to be storing the music. The music is just an interpretation of "Song". We should probably store the music on the Artist_Song entity. Doing so causes another problem; many artists re-release or re-make songs. Same title, same artist, but the song is played a little faster. I think the Artist_Song entity might need some kind of tie breaker. I don't think you will ever find an artist releasing a song in two different styles on the same day. (I'm probably wrong, you never know what those crazy artists will pull next). My guess is, if they do, the song title would be slightly different (ie Buguloo, and Buguloo dance mix). So maybe the unique key to Artist_Song should be Artist_name, Song_Title, and Release_Date.

Now... After all that, it seems apparent there is no relationship between Album and Song, and there is no relationship between Genre and Song. These are related to Artist_Song. For that matter, History, Comment, and Playlist are not related to Song, but rather Artist_Song.

There is a relationship between Artist_Song and Album, but it should be a 0:M. Not every song is released on an album. But a particular version of any song might be released on several different albums.

I think Genre would just live as an attribute on the Artist_Song entity since we have now uniquely identified a particular version of a song by a particular artist. But you may think that a given performed song might belong in multiple genres.

I hope this helped.
Russ

"stu" <smcgougan_at_yahoo.com_> wrote in message news:VnlX7.2147$ll6.428127_at_news6-win.server.ntlworld.com...
> I'm trying to design a relational database to store MP3 files.
>
> Have a look at the attached picture (I just attached it cause its only
32k)
>
> Any names with an underscore are linker entities and were created when I
> decomposed the many-many relationship.
>
> Explanation of some features:
>
> 1 song has 1 or more artists (think of 2 singers etc)
>
> Different albums can hold the same song
>
> The history table is updated every time a user plays a song. It will be
> used for creating a chart. Charts can be user specific and can run over a
> defined time period.
>
> A user can make comments about a song
>
> For a user to play a song he must first put it into a play list. A play
> list holds 1 or more songs.
>
> I am unsure of when to create indexes. If I'm posting a foreign key
should
> this be indexed? e.g. UserID in Play List
>
> I am also unsure about the right hand side of the picture. There seems to
> be a lot of loops (which I'm told are not a good thing).
>
> Any comments/suggestions are welcome.
>
> cheers
> stu
>
>
>
Received on Mon Dec 31 2001 - 20:24:25 CET