How to structure a base with many different type of assets


#1

I am new to Airtable and diving into the deep end of the pool—setting up a base to manage the collection and exhibitions for a museum.

I am managing many types of assets- physical artifacts, photos, facsimiles of historical magazine covers, books, people/biographies, events, Key dates/events, inventions, a glossary, a workflow for each exhibit. So it is part digital asset management system, and part project management. Is it better to make one giant table with lots of columns and use keywords to sort them? or create multiple tables? I watched the Airtable tutorials but didn’t get a good answer on this issue.

In terms of output, I need to be able to create timelines, add new artifacts as they come in, add new photos to existing entries, track sources, track copyright information.

Any ideas on how best to structure this would be appreciated! Are there any white papers or background reading, best practices?


#2

So it sounds to me like you have, potentially, a lot of different “types” of records, but all of which have a couple things in common, as assets – namely a “Date” component of some sort, and the need to plug in to a Project.

I would definitely suggest having a distinct “Exhibits” table, and some way to join your assets to an Exhibit.

Table 1 – “Exhibits”

  • each record represents a single exhibit with all its relevant static info; “Description”, “Start Date”, “End Date”
  • the critical field will be a link to another table, perhaps called “Pieces”; this link field will accept more than 1 linked record (as you want to add many assets to an exhibit)

Table 2 – “Pieces” (or whatever more appropriate name you would use)

  • each record represents an instance of the use of a single piece/asset in an exhibit
  • the critical fields will be the link back to “Exhibits”, which will accept a single linked record (you want a piece to only be part of a single exhibit at a time); and then a link to the asset itself, in whatever table the asset exists in
  • I don’t know if there may be other data you want here that is relevant to the union of an asset with an exhibit, regardless of what “type” of asset it is – maybe a description of the ‘position’ in the display or something?

Here is where you have 2 options for holding your assets, and which works best will depend on just how different the data is from one type of asset to another. I will post this, and describe these two options in another post, so you can get started reading this one (since I know you are on the forums at this moment).


#3

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.


#4

Hi Jeremy great input. I was just making a list of the types of things we will be exhibiting.

they fall into three broad categories-

  1. physical objects we own (equipment, documents, historical artifacts like medals, mugs, instruction manuals, posters, etc) I made a detailed list of classes and subclasses- fills about 3 pages)

and

  1. Visual media. (photos, audio recordings, video, 3D models, books, periodicals etc)

  2. Information (biographies, invention dates/descriptions, company histories, product launches, a glossary, etc)


#5

I think maybe what I need is how to diagram a database. what tables to make, how to identify dependencies. I think I will try googling that.

Will post results.


#6

Could try the free version of this: https://www.quickdatabasediagrams.com

This appears to be free: https://dbdiagram.io/home/

You could also use mind mapping software like: https://www.lucidchart.com or https://www.xmind.net


#7

we will have relatively few “exhibits” (6 to start) and relatively many “Pieces” (several hundred to start) and lots of information & media that needs to be managed (multi thousands already and we are barely begun).

I think the way to crack this nut is to make a test base that includes just a few assets of each type and see how it works.

thanks so much for helping me muddle through this-


#8

Ya, with something that could grow potentially to the scale of thousands of records, I would definitely suggest taking the time to build out a couple different versions of databases with different schemes to test them out in real usage on a few dummy records. I think the time will be well spent, as by actually using Airtable in different capacities you will discover how it works and perhaps what kind of workflow you want to prioritize as well.

You don’t want to rush into a particular structure, scale it to multiple thousands of records, and discover that the workflow is sub-optimal as compared to if you had structured it differently.


#9

#10

I have a similar database - a visual museum / collection database and one of the things I run into most of all that is annoying is that pop up to expand an entry being in a vertical format. For your use (and mine) it would be preferable to be able to populate a pop up with as much visual information in a landscape format. Scrolling up and down with a mouse is not my thing.

Further, the Airtable UI does not allow smooth and fast switching from table entry format to gallery entry format. Split screen option would be a bonus. Further, when opening a pop up for an entry that involves media it is impossible to move forward or back in the database. That is limiting.

Anyhow, what I am trying to say is that the AirTable UI falls short when attempting your sort of database.


#11

How have you solved the problem? Different DAM system? Submitting feature request? I am not far enough along in my work to have stumbled over the issues you raise yet, but thanks for the heads up.


#12

Also … I forgot … Airtable is hopeless at printing out reports with large number of visual files. If you use the Page Designer Block to create a large list of files with minimal size thumbnails, you will chomp through Gigabytes of memory, for some peculiar reason. It is not at all stable at the moment.