Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Relationdatabase and tables in my music database problem.

    Hi.

    I want to create a database in ACCESS that is a kind of overview over all the albums I have heard and/or have of an artist/artist member. Its been a long while since I made an database, so my knowledge is a bit rusty.

    The database in it self, have to contain the following:
    - Band name (for example ACCEPT)
    - Artist name (for example Eric Clapton)
    - Album (informatin about the album)
    - Genre
    - Keywords (this is to be able to search for example what a song is about, its theme)

    I have made the following tables:
    Genre
    - Genre_ID (unique, primary key)
    - Genre_Name

    Keywords
    - Keyword_ID (unique, primary key)
    - Keyword_Name


    Artist
    - Artist_ID (unique, primary key)
    - Artist_Name

    Band
    - Band_ID (unique, primary key)
    - Band_Name
    - Artist_ID_key (foreign key to Artist.Artist_ID)

    Album
    - Album_ID (unique, primary key)
    - Band_ID_Key (foreign key to Band.Band_ID)
    - Artist_ID_key (foreign key to Artist.Artist_ID)
    - Album_Name
    - Album_Cover
    - Album_Length
    - Tracks_Total
    - Track_Number
    - Track_Name
    - Track_lenght
    - Year
    - Have_Album
    - Genre_ID_key (foreign key to Genre.Genre_ID)

    The problem is 3 folded:
    1. One album can either have an artist or a band. But a band can contain several artists (members of that band).
    - Example: The band Bon Jovi (band name) got several members, amongst Jon Bon Jovi. He is a band member AND an artist, in the sence he does solo work. The problem i am faceing, is that Jon Bon Jovi is both a band member and artist. So i am not sure how to handle this one.
    2. Tracks and numbers on an album. I am not sure if i will need more tables here, but i think i might need it, since i have to link a track name, track length to a track number on the Album table.
    3. Keys. As i wrote early, i am very rusty with databases, so i don't remember much about keys. So would need some help here too.

    I would be very happy if i anyone have the time to help me, with this

    Hopefully
    Ken-Arthur

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The simplest solution consists in having a numeric column into the Album table (name it Performer, numeric). This column contains a pointer (a numeric ID) to a table of performers.

    The Performer table has (at least) 2 columns: ID (autonumber, primary key) and Name (text).

    You then have a table Artist that has (at least) 2 columns: ID (autonumber, primary key) and Name (Text).

    Finally you have a junction Table that has 2 columns: PerformerID (numeric) and ArtistID (numeric).

    When the performer is the only artist (case of a single singer or musician) there is only one row (line) into the junction table with PerformerID from the Performer table and ArtistID from the Artist table.

    When there are several artists (case of a band or orchestra) there are several rows (lines) into the junction table, each having the same PerformerID (from the Performer table) and an ArtistID (from the Artist table), this for every artist in the band/orchestra.

    The schema to retrieve the information is:
    Code:
    Album.Performer --> Performer.ID --> Junction.PerformerID --> Junction.ArtistID --> Artist.Name
                                         (if more than one artist)
                                         Junction.PerformerID --> Junction.ArtistID --> Artist.Name
                                         ... etc.
    It would be possible to simplify the design and spare one table but it would make the queries used to work with the data more complex as you would have to perform auto- (or self-) joins to retrieve the information, something I do not recommend if, as you wrote, you're "very rusty with databases"
    Have a nice day!

Posting Permissions

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