Database Structure Advice

  • Thread starter Thread starter Brad Pettit
  • Start date Start date
B

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
 
Brad,

What you need is a third table that will relate the
combinations of bands and artists. In this table you would
store the ID of the Artist and the ID of the band. A record
should be created for each instance of an artist being in a
given band. You may also want to add a 'Joined Date' and
'Left Date' if you are interested in recording the time
period that the artist was in the band. You can now use this
table as the basis of a subform on a Band form to list the
Artists involved or on an Artist form to list the bands that
they were in.

Gary Miller
 
Back
Top