Creating Many-to-Many Database - Need Help!

[sjkwapien]

I'm not new to Access, but I'm new to in-depth relationships between tables and need some serious help on figuring out how to make this work - if I can make this work. I'll try to break down my scenario then any help on how to set-up the relationships is much appreciated!



To start, we have an ongoing monthly arts event called Art Hop. At this event, we have various venues that participate and each venue has one or more artists showing their work. Not all venues participate each month and no artist can participate without a venue. We would like to track what artist shows where and when, and I'd like to avoid having to create a new table for each art hop event, but cannot figure out how to get the relationships between the three tables to make that happen.

Tables:
Artist
Venue
Art Hop

I'd love some help if anyone has any ideas! Feeling so lost!

[rpeare]

Artist table (with an autonumber PK)
Venue table (with a autonumber PK)
Art Hop event (with an Autonumber PK)

the only table you need now is

Art Hop Detail table

Detail_ID (autonumber PK)
Art Hop ID (FK to art hop event table)
Artist ID (FK to the artist table)
Venue ID (FK to the venue table)

This is the minimum you would want.

It can get a more complex depending on what your end goal is but this is likely the minimum

[sjkwapien]

Okay! Now my brain is starting to work.

So, now to some questions....

Would the Art Hop event table simply have the list of dates the event takes place? If so, would the Art Hop details table then relate to each one of the tables? I'm still thrown on how to make the connection between the single venue to a potential of multiple artists.

The breakdown above is already helping me start to process this out. Thank you!

[ItsMe]

Not sure I understand the distinction of Art Hop but, perhaps a junction table is on order. The table would have a PK column and then additional FK columns to hold values from the primary keys of tblArtist, [tblArt Hop], and other tables if needed.

This will allow a many to many relationship between Art Hop and Artist. Additional columns could include dates and descriptions or another FK to hold the PK value from a dates and descriptions table.

edit: and of course, if you can get it to work with the topology provided by rpeare, do it. Simpler is usually gooder.

[orange]

To start, we have an ongoing monthly arts event called Art Hop. At this event, we have various venues that participate and each venue has one or more artists showing their work. Not all venues participate each month and no artist can participate without a venue. We would like to track what artist shows where and when, and I'd like to avoid having to create a new table for each art hop event, but cannot figure out how to get the relationships between the three tables to make that happen.

I copied your description and underlined some key points (I hope).

A monthly Art event is called an ArtHop.
An ArtHop occurs at 1 or more Venues (Is that a Location?)
An Artist has 1 or more ArtWorks
An Artist is "shown" at 0 or more Venues. (I say 0 because an Artist may not participate in some ArtHops---correct?)

tblArtist
ArtistID PK
ArtistName
OtherArtistInfo

tblArtWork (identified since it was implied in description)
WorkID PK
ArtistID FK
WorkName
WorkMedia
OtherWorkInfo

tblArtHop
ArtHopID PK
ArtHopName
StartDt
StartTime
OtherArtHopInfo

tblVenue
VenueId PK
VenueName
OtherVenueInfo

My 30000 foot view is

ArtHop----->Venue<-----ArtWork <------ Artist

An ArtHop consists of Many Venues
An ArtWork is displayed at a Venue
An Artist has 1 or many Artworks

You may get more info from this free data model, This for concepts only and is not specific to you. It is meant as a starting point.
http://www.databaseanswers.org/data_...ions/index.htm

Good luck

[rpeare]

This is the way I would design it, but I don't know how complex you want to make your database.

table tbl_Artist

ArtistID	ArtistName
1	Artist 1
2	Artist 2
3	Artist 3

table tbl_Venue

VenueID	VenueName
1	Venue 1
2	Venue 2
3	Venue 3

table tbl_ArtHopEvent

Event_ID	Event_Date	Event_Name
1	1/1/2014	First Event
2	2/1/2014	Second Event
3	3/1/2014	Third Event

table tbl_HopVenues

HV_ID	Event_ID	Venue_ID
1	1	1
2	1	2
3	2	1
4	2	2
5	2	3

In other words for event 1, venues 1 and 2 are participating
for event 2, venues 1 through 3 are participating

tbl_VenueArtists

VA_ID	HV_ID	Artist_ID
1	1	1
2	2	1
3	2	2
4	2	3

in other words, for HV_ID 1 (Event 1, Venue 1) only artist 1 is showing material
for HV_ID (Event 1, Venue 2) artists 1, 2 and 3 are all showing material

[walpy]

Artist table (with an autonumber PK)
Venue table (with a autonumber PK)
Art Hop event (with an Autonumber PK)

the only table you need now is

Art Hop Detail table

Detail_ID (autonumber PK)
Art Hop ID (FK to art hop event table)
Artist ID (FK to the artist table)
Venue ID (FK to the venue table)

This is the minimum you would want.

It can get a more complex depending on what your end goal is but this is likely the minimum

As I am pretty new to Access, I'm trying to teach myself by doing. Can I ask what exactly does PK and FK stand for?

[rpeare]

pk is primary key
fk is foreign key

Primary key is the unique identifier of the table currently being viewed
Foreign key is the unique identifier of a FOREIGN table from the one currently being viewed

[orange]

walpy,

Here's a tutorial that you should work through. You will learn from it.

[walpy]

walpy,

Here's a tutorial that you should work through. You will learn from it.

Thanks, I'll go through it for sure!


And thank you as well rpeare!

[sjkwapien]

You've all been so helpful, thank you! I'll give some of these a try and see what turns up.