Database Structure Question

[Brad Pettit]

I am having trouble picturing how I should setup fields for a database I am
makng.

I am making a database version of Rock Family Trees. Basically, I want to be
able to have a form based on Artist or Band member, and see who was in the
band, or which bands this artist has been in.

The biggest problem I am having is with people who have been in multiple
bands. For example, Eric Clapton has been in the yardbirds, cream, blind
faith, and solo. I am assuming I would make two tables, one for artists and
one for bands, then use keys to point them at each other. but how would I do
it with multiple artists or bands? I understand that I could make a field
called BandID and enter in "Yardbirds" for the Eric Clapton entry in the
Artist table, but where would I tell it "Cream" and etc?

I wouldnt have to make 6 different band or artist ID's for each table would
I? I understand that once I figure a way I would use one of them as a
subform and with relationships it would bring them in. Can someone explain
this to me?


Thanks,
Brad

 

[Ken Snell]

What you want is a "junction" table to handle the many-to-many
relationships.

This table (call it tblArtistBand) should have these two fields:
ArtistID ( make it the primary key in conjunction with BandID)
BandID ( make it the primary key in conjunction with ArtistID)

This table structure allows you to have one artist be associated to many
bands, and many artists be associated to one band. By using the composite
primary key, an artist can be assigned to any specific band just once (no
duplicate entries).