microsoft.public.access

"Beetle" wrote:

> Yes that's correct if that's how YOU want it to be (sorry, I should have been
> more clear about that in my post). Perhaps you always limit an artist to
> belonging to only one genre (I don't know), in which case you would put
> GenreID in the artists table (as a Foreign Key to the Genres table) and you
> would just have a One-to-Many relationship between Genre/Artist.
>
> If you don't want to limit an artist to only one genre, then it *seems* to
> me that the best way to link the Artist/Genre would be through the albums
> table. In other words, an artist wouldn't belong directly to a genre, only
> their albums would. I've never actually created a music DB before, so this is
> just my opinion (for what it's worth). It all depends on what your
> preferences are.
>
> HTH
> --
> _________
>
> Sean Bailey
>
>
> "Chip" wrote:
>
> > You mention in your last paragraph - "This structure assumes a Many-to-Many
> > relationship between Artists and Genres (a genre can have many artists and an
> > artist can be in more than one genre)."
> >
> > If I have table Artists (one side) -> albums table (many side)
> > and also table Genre (one side) -> albums table (many side)
> >
> > does that equal a many-to-many relationship between Artists and Genres?
> > That's the only part I am finding a bit confusing right now.
> >
> >
> > Thanks for your help, I really appreciate it.
> >
> > --
> > chip
> >
> > I have -
> >
> > artist table -
> > Artist_ID (PK related to Aritst_ID on albums table) (one side)
> > Aritsts
> >
> > albums table -
> > Album_ID (PK) (related to Album_ID on songs table) (one side)
> > GenreID (FK to Genre table) (many side)
> > Artist_ID (FK from artist table) (many side)
> > +others
> >
> > Genre table -
> > GenreID (PK related to GenreID on albums table) (one-to-many)
> > GenreName
> >
> > Songs table -
> > Song_ID (PK)
> > Album_ID (FK to Albums table) (many side)
> > FileID (FK to Files table) (many side)
> >
> > Files table -
> > FileID (PK related to Songs table) (one side)
> > FileLocation
> >
> >
> > "Beetle" wrote:
> >
> > > If you have music from many different genres, then you may want to add a
> > > table for that also. Maybe something like the following;
> > >
> > > tblArtists
> > > *******
> > > ArtistID (Primary Key)
> > > ArtistName
> > >
> > > tblAlbums
> > > *******
> > > AlbumID (PK)
> > > ArtistID (Foreign Key to tblArtists)
> > > GenreID (Foreign Key to tblGenres)
> > > AlbumName
> > > YearPublished
> > > Format
> > >
> > > tblGenres
> > > *******
> > > GenreID (PK)
> > > GenreName
> > >
> > > tblSongs
> > > *******
> > > SongID (PK)
> > > AlbumID (FK to tblAlbums)
> > > FileID (FK to tblFiles)
> > > SongName
> > > Notes
> > >
> > > tblFiles
> > > *****
> > > FileID (PK)
> > > FileLocation
> > >
> > > This structure assumes a Many-to-Many relationship between Artists and
> > > Genres (a genre can have many artists and an artist can be in more than one
> > > genre). The relationship between the artist and the genre is defined by
> > > tblAlbums. You then have One-to-Many relatonships between Albums/Songs and
> > > Files/Songs.
> > >
> > > HTH
> >