A Database Model for Gallery or Museum Management

What kind of database model does it take to run a gallery or museum? How can it be optimized to manage events, partnerships, and other activities?

When I think of a gallery or museum, I usually think of a peaceful place where you can hang out for hours, looking at interesting or beautiful things. Personally, I enjoy visiting the Technical Museum in Zagreb, which has cool exhibits like old cars, fire trucks, submarines, trams, and trains.

But this article is not about me. Today, we’re going to imagine being the director of a museum or gallery. What data is crucial to this job? What kind of database model will be needed?

What Can We Expect in a Gallery…

… As a Visitor?

A visitor will usually buy a ticket, walk around, and look at exhibits. Visitors can expect that the gallery will have at least one permanent exhibition (which may have different items over time). Visitors can also expect to see a travelling or temporary exhibition, which may sometimes partially or completely replace the permanent exhibition. Galleries generally replace exhibitions more frequently than museums for at least one simple reason – it’s easier to move pictures than locomotives! ☺

… As a Manager?

Managers have much more to do! Each location can have one or more halls, and managers may oversee more than one location. Different expositions could take place at various times. Maybe there are two or more expositions in different halls of the same gallery at the same time. Managers need to know who is in charge of which task. Who are the partners? What are the roles of the museum employees for each exhibition? When does an exhibition or a role start and end?

Obviously, there are many questions involved in managing a museum. We’ll find some answers in our model.

The Database Model

I split the model into four subject areas:

• “Gallery”
• “Event”
• “Items and Location”
• “Roles, Staff and Partners”

We’ll start with the Gallery and Event areas because they contain the most interesting tables. Then we’ll move to the Items and Location area and finish with the Roles, Staff and Partners area.

The Gallery Area

Before we can even think of organizing any event or exhibition, we need a place to house it. That is where the Gallery subject area comes in. It contains four tables, and we’ll take a look at each of them now.

The gallery table is the central table in this section. In most cases, we’ll have only one gallery and it will be at a single location. But we may have several galleries at different locations. For each location or gallery, we’ll define its name and the street address and city where it’s located. All attributes in this table are mandatory.

Galleries usually have more than one hall (or exhibition space), so we’ll need a table to store those as well. Besides gallery_id, the hall table only has one attribute, hall_name. We’re calling this attribute hall:Gallery here because we’ll use its clone in the Items and Location area.

The other two tables in this section are the city and the country tables. Once again, all attributes in these tables are mandatory. In the city table, the postal_code – country_id attribute pair form the alternate (unique) key of the table. In the country table, the only attribute besides the primary key is country_name. That attribute is also the alternate key of this table.

The Event Area

Now we have our gallery and a place for events and exhibitions. We can move on to the events themselves, which are stored in the Event area. This is a pretty small section of our model, but it’s still very important. We can understand this area better when we consider that each change inside the gallery is basically a new event. We’ll relate each event with whoever is in charge of it and with other details (like the event’s location inside the gallery and the items that are displayed).

Obviously, the event table is the most important one in this section. Like the gallery table in the previous section, this table is pretty simple. For each event, we’ll store its name, a description, and the gallery that organizes it. And once again, all attributes in this table are mandatory.

The other two tables have almost identical structures and roles. We’ll use the event_staff table to store all the employees or staff members working on that event and their individual roles. The event_partner table holds information about the event partners and their roles. For now, we won’t explain how roles are stored. We’ll assume that the foreign keys in these tables denote both users and roles. However, we will take a quick look at the common attributes in these two tables:

• event_id – This is the ID of the event that the staff member or partner is involved in.
• staff_role_id / partner_role_id – This is the ID of the staff member or partner and their role.
• start_date – This shows when the role started.
• end_date – This is the date when that role ended. It is arbitrary because we may not know this date in advance.

The Items and Location Area

The tables in the Event area provide us with a place to store information about exhibitions and other happenings, including the people and organizations involved in them. To round out the whole event-planning process, we’ll need “stuff”. In the case of a gallery or museum, that “stuff” is made up of items like pictures, statues, antiquities, etc. Notice that the hall table here is actually a shortcut made to avoid relation overlap in the model.

A list of all the exhibit items, like pictures and statues, that have ever been part of an exhibition is stored in the item table. All fields in this table are descriptive and mandatory. They are here to give us more details about each specific exhibit.

If we know the author (i.e. painter, sculptor, etc.) of that item, we’ll first store that data in the author table and then establish a many-to-many relationship with the item_author table.

In the author table, the author_name and description attributes are mandatory. We can’t always be sure we’ll know the exact birth and death date of each author, so these two attributes are arbitrary.

In the item_author table, the item_id – author_id pair forms the unique key for the table.

The location table is used to store exactly where the item is placed during the exhibition. This information will help both managers and visitors; they’ll be able to find their way to various items using an exhibition program or a map. The records stored in this table will also keep track of all exhibitions that have displayed a certain item. The attributes in this table are:

• event_id – This is the ID of the event where the item was shown.
• item_id – This is the item’s ID.
• hall_id – This is the ID of the hall where the item was placed.
• description – This is an additional description, if needed, of the item’s location.
• time_from – This is a timestamp denoting when the item was placed in that position.
• time_to – This is a timestamp denoting when the item was moved from that position.
• loaned – This Boolean value is set to “True” if the item was on loan from another institution or a private individual.
• loaned_from – This is the name and description of the person or institution that lent the item.

By using this level of organization, we allow a gallery to have:

• Multiple exhibitions running at the same time in different halls. This is very important for large galleries or for groups of galleries.
• Dynamic exhibitions. We can move items from one hall to another if the need arises. Usually it doesn’t, but you never know.

The Event and Items and Location areas provide gallery managers with the ability to store all the details of the staff, partners, and items related to an event. If some events repeat over time, they could use the data from a similar event as a template for the new one. For example, a manager might want to resume a permanent exhibition after temporarily giving its space to a visiting exhibition. These records will allow the manager to re-create the original exhibition accurately.

The Roles, Staff, and Partners Area

This area may not be specific to a museum or gallery database model, but there is at least one interesting concept I would like to point out. We’ll have both employees and partners engaged with events. The partners could be private individuals or legal entities (e.g. a radio station that is the media sponsor of an exhibition).

Therefore, we’ll need to have two “types” of roles – one for employees and another for partners. To avoid having two separate tables in the model, I structured the role table this way:

• role_name – This is the actual name of the role.
• is_staff_role – This field contains a “True” value if the associated role can be assigned to employees. If this attribute is “True”, then the is_partner_role attribute is “False”. Examples of employee roles include “event manager” and “guide”.
• is_partner_role – This contains a Boolean value that is “True” if the associated role can be assigned to a partner. If this attribute is “True” then the is_staff_role attribute is “False”. Examples of partner roles are “media sponsor”, “sponsor”, and “transport company”.

The combination of these three attributes forms the unique key of this table.

We’ve already mentioned staff members and partners. We could have a lot of attributes for both parties, but let’s stick with the basics for now. Each staff member will have a record of their first_name and last_name in the staff_member table. Of course, both attributes are mandatory. Since partners can be private individuals or organizations, each partner will have only one field, the partner_name attribute, in the partner table.

The final step is to relate staff members and/or partners with roles and galleries. We’ll use two tables for this, the staff_role table and the partner_role table. Since they have an almost identical structure, I’ll explain them both here:

• gallery_id – This is the ID of the gallery where the staff member or partner is engaged.
• staff_id / partner_id – These are IDs for staff members and partners, respectively.
• role_id – This is the identifier of the partner’s or employee’s role.
• role_start_date – This is the date when that role started.
• role_end_date – This is the date when that role ended.

Notice that the staff_role table and the partner_role table are related to three other tables: the gallery table, the role table, and either the staff or partner table. These three foreign keys do not form the unique key because we can have the same combination with a different role_start_date. I also decided not to form a unique key out of these four attributes (the three foreign keys + role_start_date) because we need to check for overlap with existing records before we insert a new record into the staff_role or partner_role tables.

The data model presented in this article supports most of the common functions that gallery or museum managers need. But there is something more we could add. Please share your ideas and help us make this model even better!