Many to Many Relationships

[Karen Oswald]

I have a database where we are keeping track of artists and their art. I have one table for the art and another table for the artists. Each piece of art may have more than one artist and each artist has created more than one piece of art. There are over 10K pieces of art and only 20 artists. I have heard that Lookup tables are not a good idea to use but I don't want to enter the artist information repeatedly in the table for the art work. What would be the best way to relate this data without having to enter artist information in every record in the art table?

[mshmyob]

By putting a Bridge table between the two.

tblArtist --> ArtistID, ArtistFName, ArtistLName, etc. etc.
tblArtWork --> ArtID, ArtTitle
tblArtistDetail --> ArtistID, ArtID

Also read up on normalization.

cheers,

[Karen Oswald]

Thanks for the help :)

[mshmyob]

You're welcome. Good luck with your project.

cheers,