Results 1 to 12 of 12

Thread: Database Design

  1. #1
    Join Date
    Sep 2008
    Posts
    7

    Database Design

    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!
    Attached Thumbnails Attached Thumbnails database1.jpg  

  2. #2
    Join Date
    Sep 2008
    Posts
    7
    Has nobody an opinion on this database?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2008
    Posts
    7
    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.
    Last edited by svenre; 09-14-08 at 12:43.

  5. #5
    Join Date
    Sep 2008
    Posts
    7

    new design

    some changes ;-)
    Attached Thumbnails Attached Thumbnails database1.jpg  

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by svenre
    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.
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2008
    Posts
    7
    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?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Sep 2008
    Posts
    7
    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.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by svenre
    ...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
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Sep 2008
    Posts
    7

    okay

    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!
    Attached Thumbnails Attached Thumbnails database_new.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •