dealing with an unknown number of events

[merlin777]

I'm designing a database to track paintings. One of the things i need to record is where they've been exhibited. They may be exhibited any number of times from none up to an unlimited number of dfferent exhibitions.

I'm not sure how to approach this with an indefinite number of events. I can't have a variable number of fields, i can't creat a huge number of empty fields that probably won't be used and i can't use a table for every painting because there'll be hundreds.



Any thoughts?

[pbaldy]

Events are records, not fields. I see 3 tables: paintings, events, and a many-to-many junction table to record a painting being at an event.

[merlin777]

so i need a table which records all exhibitions, but how do you specify which of those exhibitions each painting has been to?

[pbaldy]

In the third table. You could use a form based on either the events table or the paintings table, depending on how you want to view, and a subform bound to the third table. There's also this for adding multiple items at once:

http://www.baldyweb.com/MultiselectAppend.htm

[merlin777]

what i'm not understanding is how the paintings table, for each painting, records which exhibitions it's been to. if its been to exhib A and exhib B, how does that appear on the painting table?

Also not quite sure how record the dates for the same reason. A venue may not be temporary and a painting may go there several times.

[pbaldy]

It doesn't, it appears in the third table. At that point it's like any other one-to-many relationship. One painting can appear in many events, thus you use a related table.

You could include from/to dates in the junction table, and a painting could appear in that table multiple times with different dates for the same venue.

[pbaldy]

Perhaps clarifying, the third table might look like:

Painting...Event....StartDate...EndDate
.....1...........A..........9/1/15......9/15/15
.....1...........B..........9/16/15....9/25/15