Very Basic DB (Relationships)

[SilentPirate007]

I've recently been introduced to databases (access) and i'm looking for some help on building the relationships.



--The theme is a personal music database

The tables:

Songs

Song Code
Title
Artist
Album
Playtime
Year
Composer
Producer

Artists

Artist Code
Artist
Gender
Nationality

Albums

Album Code
Album
Year

Genres

Genre Code
Genre

Any help and/or hints?

Thanks

[kimmer]

Songs are tougher than they look. A song can have many artists, and therefore can have many playtimes. It depends on how you want to do it though. Do you say that 'Me and Bobbi McGee' is a song, or is it several songs (one by Willie Nelson, one by Roger Miller, and another by Janis Joplin)?

I see that you have Album as a FK to Song. What about songs that are on more than one album?

What about songs tht have multiple artists? Willie Nelson joins Toby Keith on a song or two on one of Toby's albums. You get to decide if you are going to account for that in your db (right now, you aren't).

Many songs have multiple composers.
But, this is a personal database and I wouldnt fault you for whatever you do.

Aside from all that, your model is coming along.

Will you make a junction table between Genre and Album, or Genre and Song, or what?

[SilentPirate007]

Thanks for your reply...

I'll try and append your input.

I wont be picky with the small stuff...

I will, for example, use a single artist for each song.

Since I mostly have mixed artist CD's, i'll need one album to many artists.

Thanks again.

[SilentPirate007]

Will you make a junction table between Genre and Album, or Genre and Song, or what?

Yes, I will need some sort of junction there -- not too sure what yet..

[TheShabz]

Heres what I would do. Insert a junction table for every M-M

Song M-- MArtist
(assuming artistA ft. artistB will each be credited. However if youre going by the artist of the album, make it Song M---1Artist. also i wouldnt worry about the same song with different artists as song name isnt the unique identifier. 1 song, 1 artist, 1 album is enough to not confuse playtime)
Genre 1---M Album
(this can be tricky. do you want to attribute a whole album to a genre or do you want to go on a song level? For example, if you look at those collection series like NOW! Thats What I Call Music, they have songs from many genres. If you want to go on a song level, omit this relationship and use Genre 1---M Song instead.)
Album 1/M---M Artist
(see earlier explanation)
Album 1---M Song.