Hello all,

I am new, and have a question. What do you think about the database that I designed. Is there something missing? Is it in 3NF?

Description:

1. A band can have many albums and also many songs.
2. An album can have many songs.
3. Songs can get a rating from a user; many users can rate a song. A user can also leave a message, reason for his rating.
4. A Band can have many members, but a member can also play in several bands during his career.
5. A Band is playing some sort of genre, for example: Rock, Pop, Electro, Soul, Funk, Jazz, and so on.

Thanks for your answers!

Has nobody an opinion on this database?

can a song appear on more than one album
although a band wil havve more than one member, how do you propose to handle who wsa a memebr of the band when a specific album or song was written or recorded. a member of the band may appear one one album but not another, some bands have member who come and go, ie may appear one several albums but not consecutive albums
how do you propsoe tohandle difference between waht are notionally the same album.. there may be ore tracks on one version of the album (eg say a tape or CD version, or a UK as opposed to a US version)

I'd of thought a song exists, it may appear in one or more albums

when a song appears on an album it probably should have a track ID and say a time/length... quite often songs are modified to suit an alum (eg difernet time, different tempo

I reamin to be convinced that genre is applicable to bands.. fine as a default genre, but the actual genre may change as the bands changes. also sometimes bands peform a song in a different genre (several 'rock' bands have
done parody's of Country and Western.

you may be better of referrign to performers in place of bands.. that allows you to cater for single performers

you may be better off referring to artists in palce of performers

there is an argment that a performer may have one or more specialites (eg singing, bass, drums etc)

Hello Healdem,

thanks for your answer, conclusion:

- A song can appear in more than one album, so there is a many to many relation ( I will need a bridged table), also length is a good attribut
- the idea to say performers instead of band is good. The problem with the genre is yeah, perhaps i will let it be for firswt time
- of course a band member can play different instruments, and also have a favourit one

BUT: The fact that a artist can play for several bands (performers) is already modelled. There is a bridge table with two attributes (start and end as date), when he starts to play and stops playing for them. I think I have to do some renaming, too, so that it is clearer.

some changes ;-)

fine
but I dont see any mechanism that identifies what artisted comprised the performers on a specific band. there is no way of identfyingwho performed on what tracks. there is no way of tracking who was a memebr of the band when album X or track Y was recorded
there is no way of identifying when a specific artiste was perfomring with the band,
bear in mind that an artisite has a separarate life/existence from the band. a performer may be n sevrak bands at the same time, a performer may take umbrage and leave a band for a period of time which may or may not happen whilst he band records one or more albums or songs.

yes, there are no direct connections between artist and song for example, but there are indirect connections. If you have a look at the song, than you can see which band has created it (maybe a band is only one artist). And of course if you know the band one can come to the artists. Thats SQL!

Of course you can see when a specific artist is playing for a band, there is a bridged table with the start and end time a artist plays for a band.

Perhaps you have a different point of view?

a song may appear on ore tan one album in mnore than one form
.....ferinstance a performer
may do an acoustic version, they may do an altered length version, they may do one with alternative lyrics....
for some peole the treak ID is important
the song may be covered by other performers who does all 3

I would have expected the 'label' to be the record company.. mebbe that needs to be a FK to a table containing details of record labels. I'd also expect the labels record no to appear somewhere.

Im surprised to see that the user rating requies its own table

Anthologies?

Collections of hits by various artists?

What about when a musician plays 'guest roles' on albums. (Jackson Browne is in Warren Zevon's 'Werewolves of London' album, for instance, at the same time that he is in his own band.)

I am thinking about your suggestions and change the database in some details tomorrow. Let me see, what I can do.

But the user rating should be clear: A user can rate many songs and a song can be rated by many user, so there is a many-to-many relation and a bridge table is needed, of course.

by bridge table I presume you mena intersection table?

I'd expect the PK to the user, the PK to the specific song and the rating and arguably a date or any other relevant data.

I'd also suggest the rating applies to a specific recording of the song (by a specified performer on a specified album/record media)... why. quite often a popular record gets releasd by more than one performer, and more than once by the same performer. a user may like version 1 by band A, but dislike the version by band z. the same user may prefer say the 2005 version of the song as opposed to the 2008 version.

ferinstance
personally I dislike the Rod Stewart cove of Sailing, I far prefer the original by Sutherland Brothers
I dislike classical recordings by Berliner Symphonik with von Karajan... they are so soulless and lack any emotion or artisitic intepretation

changed some facts:

- now they are interpret
- there is a relation between artist and song (Many-to-Many) because also several artists can make a song
- then there is the date in the userrating
- one further table for the label

see attachment

Thanks a lot for your help!