By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

question from beginner...

P: n/a
I was thinking of making a music database i've created tables but I
cannot make a good relationship out of those tables... here are the
tables I created in access:

CD(cdno,created on)
Cassette(label, cassette no) <has artist(artistno,cdno,cassette
no, artist) <has album(albno,artistno,albumname) <has>
artist(.....)

here is the problem ,

one artist can have many album, and one album can have many artist...
how can I show this relation in access...

Sep 5 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"haha" <pr**************@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
>I was thinking of making a music database i've created tables but I
cannot make a good relationship out of those tables
<snip>
>
one artist can have many album, and one album can have many artist...
how can I show this relation in access...
One album can have many artists? Well, OK, you could try using a many to
many relationship by using a "join" table between your artist and album
tables.

tblAlbum
ID
AlbumName
etc ...

tblJoin
AlbumID
ArtistID

tblArtist
ID
ArtistName
etc ...

Join tblAlbum.ID to tblJoin.AlbumID in a one-to-many and join tblArtist.ID
to tblJoin.ArtistID in a one-to-many.

Keith.
www.keithwilby.com
Sep 5 '06 #2

P: n/a
There are many many things to consider and it depends what you want to do.

For instance you could have a Recording table and a RecordingRelease table
to deal with multiple formats LP, MC, CD, SACD, MP3, DVD-AUDIO the
RecordingRelease would contain data specific to the format and the Recording
would contain data about the recording whatever format it was released in.

It would probably be crazy to have a table for CD and one for Musicassette
and one for......

As to Artists and Recording/RecordingRelease you will need to resolve the
Many to Many relationship as per the usual approach with a table with a 1 to
Many relationship from Artist and a 1 to Many from
Recording/RecordingRelease.

It depends entirely upon the use you wish to put the database to.

A system designed for Sony may differ to one designed for a private
collection.
--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

"haha" <pr**************@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
>I was thinking of making a music database i've created tables but I
cannot make a good relationship out of those tables... here are the
tables I created in access:

CD(cdno,created on)
Cassette(label, cassette no) <has artist(artistno,cdno,cassette
no, artist) <has album(albno,artistno,albumname) <has>
artist(.....)

here is the problem ,

one artist can have many album, and one album can have many artist...
how can I show this relation in access...

Sep 5 '06 #3

P: n/a

Keith Wilby wrote:
"haha" <pr**************@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
I was thinking of making a music database i've created tables but I
cannot make a good relationship out of those tables
<snip>

one artist can have many album, and one album can have many artist...
how can I show this relation in access...

One album can have many artists? Well, OK, you could try using a many to
many relationship by using a "join" table between your artist and album
tables.

tblAlbum
ID
AlbumName
etc ...

tblJoin
AlbumID
ArtistID

tblArtist
ID
ArtistName
etc ...

Join tblAlbum.ID to tblJoin.AlbumID in a one-to-many and join tblArtist.ID
to tblJoin.ArtistID in a one-to-many.

Keith.
www.keithwilby.com
I did try that way... what happened was when I created a query to
display what happens is for example:

ARTIST - Robbie Williams
ALBUM - NOW 40
SONG - Kids
ARTIST - Britney Spears
ALBUM - NOW 40
SONG - Kids
ARTIST - Britney Spears
ALBUM - NOW 40
SONG - Lucky
ARTIST - Britney Spears
ALBUM - NOW 40
SONG - Kids

What I want is:
ARTIST - Robbie Williams
ALBUM - NOW 40
SONG - Kids

ARTIST - Britney Spears
ALBUM - NOW 40
SONG - Lucky

How can I sort this out??

Sep 6 '06 #4

P: n/a

Craig Alexander Morrison wrote:
There are many many things to consider and it depends what you want to do.

For instance you could have a Recording table and a RecordingRelease table
to deal with multiple formats LP, MC, CD, SACD, MP3, DVD-AUDIO the
RecordingRelease would contain data specific to the format and the Recording
would contain data about the recording whatever format it was released in.

It would probably be crazy to have a table for CD and one for Musicassette
and one for......

As to Artists and Recording/RecordingRelease you will need to resolve the
Many to Many relationship as per the usual approach with a table with a 1 to
Many relationship from Artist and a 1 to Many from
Recording/RecordingRelease.

It depends entirely upon the use you wish to put the database to.

A system designed for Sony may differ to one designed for a private
collection.
--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider
I did think of making a different table for recording but I couldn't
think of not more than one attribute for that table...

Sep 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.