Database Structure Advise

[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

 

[Bryan Martin]

C-Inline

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?

Table1 (Artist)
ID
Name

Table2 (Band)
ID
Name

Table3 (ArtistMatrix)
ID
ArtistID
BandID


Fill in your artists and bands. Then inside the artistmatrix place as many
artistID's in as many BandID's as you need. Now when you need to look up
info on your artist use the inner join to build relationships between your
tables.

Bryan

 

[Nigel tombs]

Brad.
You need 3 tables to overcome your "many to many" relationship

Table1: Artists(ArtistID,firstname,lastname)

contains e.g
1,eric,clapton
2,mike,reid


Table2: Bands(BandName)

contains e.g
yardbirds
cream
blind faith


Table3: ArtistsAndBands(ArtistID,BandName)

contains e.g
1,yardbirds
1,cream
1,blindfaith
2,cream