If you can come up with a relatively small number of “general” data fields that could be used to describe/provide critical information about any asset, regardless of what type it is, then you could have a single “Assets” table, where each record represents a single asset and its information, a critical field being “Type”, which could likely be a Single Select “tag”-like field, and could be used for filtering/grouping in views to organize/analyze your assets even though they are all thrown into the same table together. This approach requires you to be able to generalize most of the other information about an asset though – you likely don’t want to have 50-60 fields, only 8-10 of which will be filled out for any given “type” of asset; that would just be messy, in my opinion. If you could consolidate that down (by generalizing the nature of the data in those fields) to, say 10-20 fields, the majority of which are filled out for any given “type” of asset, this approach would be much more manageable.
The advantage of this approach is that you now only have a single point of linking to a “Pieces” record. A “Piece” contains one link to an exhibit, and one link to an asset, and regardless of the “Type” of asset, that link is pointing at the same table, because all the assets are in one table.
The other solution is to have a new table for each “type” of significantly different asset. In the “Pieces” table, this means a field to link to each asset “type” table you have, which means potentially a large number of linked record fields, all of which will be empty except 1 when you are making a “Piece” in an “Exhibit”. This represents its own kind of “messiness”. But the advantage here is that each “Asset Type” table can have exactly and only the data fields you need to describe that kind of asset. This feels, to me, like the better option, as the messiness in the “Pieces” table feels like a more manageable situation. When you are creating an “Exhibit”, and adding “Pieces” to it, you will have to decide whether you are linking to a “Book”, or an “Article”, or a “Photo”, and use the appropriate linked record field, ignoring all the others – but the Asset record itself, whether a “Book” record or a “Photo” record, will be clean, containing only the relevant info about that asset type.
There are other considerations to take into account that depend on what kind of data you need to pass back and forth between tables, and how in depth the “Project Management” aspect of planning your Exhibits needs to be, but I think this should at least give you a couple approaches to consider.
