donation type in museum database

[wally buch]

I am trying to resolve the following issue. I am building a database for a non profit museum. One donor can have many gifts. But it is also possible for a donor to loan an item rather than gift it. Do I simply need a field under the gift table that states whether it is gifted or loaned, or should I have a separate table for each type, i.e. gift or loan. This is made more complicated because a loaned gift can later turn into a donation.

Any help is greatly appreciated.

w buch

[doughemi]

I think you should have one table for exhibits. Then you would need a Transactions table that would have a donor id field related to donor id in the Donors table, and an exhibit ID field related to the ID field in Exhibits. The details of the transaction (type, date, disposal date, etc) would be in this table. When an exhibit changes from a loan to a gift, a new record would be generated in the Transactions table. The Transactions table would also record when an exhibit was destroyed, sold, or otherwise removed from inventory. These would enable you to maintain historical records.

[wally buch]

This is very helpful. Do you see a need for more than the three tables, i.e. donor, join field of transactions, and exhibits? How would i handle the unusual case where more than one donor is involved in gifting or loaning an exhibit?

thanks

[doughemi]

This is very helpful. Do you see a need for more than the three tables, i.e. donor, join field of transactions, and exhibits?

Oh, yes: As soon as you build it, you will get the "can you make it pink and hang from the ceiling" questions from the client But seriously, there may be a need for exhibit classifications, or other descriptive data about exhibits, which will necessitate more related tables. The first thing to do is to sit down with the board or manager or whoever commissioned the project and hash out EXACTLY what they want to see/search/track.

How would i handle the unusual case where more than one donor is involved in gifting or loaning an exhibit?

If that is a real issue, then the Donors table will have to be a join table between an Individuals table and the Transactions table. This is a perfect example of what I was saying above about learning the exact dimensions of the project.

[Wickerman]

Wally -- it's a little hard to make definitive suggestions in a case like this without knowing / considering more about what the overall database is meant to do for the museum.

My initial reaction is that if the field set you have for "Gifts" is essentially identical to the field set you'd apply to a "Loan" then it makes sense to just have a Field like "Type" with the two values Gift/Loan to choose from. Maybe the table name should be more neutral - like 'Acquisitions' or something.

But, for instance, it might be that the database is mostly designed for keeping track of *objects* (an inventory with metadata about the object itself as well as provenance and exhibitions) . . . or is it more of a system meant to keep track of *people* -- donors / lenders -- perhaps for the purpose of museum public relations / development / taxes / fund raising . . .

Doughemi's response is on target -- design decisions need to be made with an eye to how finely-grained the data needs to be, and how the users will need to manipulate it. The multiple-donor scenario is a great example of a place where you have to ask whether the actual use of the database and the frequency of this situation arising warrants building out a whole join-table structure. If you're auto-generating thank-you letters to donors using this system, then yeah, you probably need it? If not, maybe you consider acceptable work-around compromises.

For instance, I made a system for an art collector with a table of artists and a child table of artworks. He has just a few works that are collaborative works between 2 or more artists. But this was unusual enough that we decided together it wasn't worth the costs of making a "one artwork can have many artists" join table. Instead, for collaborations he just enters that team as a new artist record and muddles through.