Design bases all linked to a single key

[IrisDR]

Hi

I am a curriculum coordinator in higher education and have multiple bases/tables/forms that contain/gather information about course units in the curriculum. All information always relates to a course unit, e.g. Business Economics. All the information needs to be linked so I can always look up information in other bases/tables, but I'm not sure when to create a new base, create a new table in a base or create a new view. These are some of the data I have:

1. Curriculum 'technical information': per course unit how many credits, elective or not, prerequisites, timing (SEM1 or SEM2) etc.
2. Curriculum task allocation: information that comes from an export from our CRM which shows which lecturer will be teaching a particular course unit in which semester(s) to which class group. The unique key here is a formula field concatenating course unit - class group - semester - lecturer, e.g. Bachelor Thesis - Class Group 2 - SEM2 - John Doe. There should be a one-to-many relationship here, where one record in the curriculum-base/table should link to multiple records in the task allocation base.
3. Curriculum timetabling: information I gather from a form requesting information from lecturers regarding how they want their course unit(s) to be scheduled, e.g. collaborative room or theatre, online or on campus, 2 consecutive hours/week or 2 separate hours etc.
4. Curriculum content: information I gather from a form requesting information from lecturers regarding the content, e.g. learning goals, registration deadline, mandatory attendance or not

In an academic year, I often gather more information regarding course units via forms, e.g. I would ask lecturers to let me know if they have any guest speakers or events planned for their course unit, or I would ask them to submit the deadlines they have for assignments to make sure not all deadlines are at the same time for the students etc.

All this information is always linked to a course unit in the curriculum. If a course unit is deleted, added or renamed, then this should be deleted, added, changed across bases/tables/forms.

I now have one base with multiple tables for this. The first table is just a list of all the course units (Curriculum). In each subsequent table, I have the same first two columns: Course Unit Key and Course Unit. Course Unit is a Linked field that is linked to Curriculum, the Course Unit Key is the primary field which is a formula field showing the content of the Course Unit field. I copy paste the curriculum course units to the linked fields of each table. This setup doesn't seem right: when I add or delete a course unit from the first table, I need to manually add/delete them in all subsequent tables. Moreover, I will end up with more than 20 tables in one base at some point.

I hope someone can help me set this up right!

[ScottWorld]

Unlike traditional database systems, Airtable doesn’t use the concept of primary keys. In Airtable, there is no concept of automatic relationships based on primary keys. In Airtable, you always need to manually link records (or manually setup your linking logic through an automation).

Anything that is a one-to-one relationship should all be in the same table, and then you can use different views or different interfaces to view the data in different ways.

If you have a one-to-many relationship, Airtable never deletes the “many” records if you delete the “one” record. This can only be done through writing JavaScript programming code or by using a no-code automation tool like Make’s automations and integrations for Airtable.

If you want your professors to be able to update their course information or add information to a course such as dates, this is challenging to do with Airtable’s forms but it can be done. However, the expert Airtable consultants like myself typically recommend using Fillout’s advanced Airtable forms for this, which allows you to easily update existing Airtable records through a form.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

[TheTimeSavingCo]

Hmm, for the deleting of a course unit, what if we added a "Status" field in your "Curriculum" table to indicate whether the course was still active or deleted?  We could then add lookup fields to the other tables to display the course unit's status, and filter as needed.  This way you'd only really need to delete records when you're hitting the record limit, and at that point you could either just duplicate the base without any records in it or figure out a way to delete all the old stuff manually

You could set up an automation with a script to help you delete the records too if you'd like?

--
For the adding of records, you could create an automation that would create the records in all your tables for you whenever a new Course Unit record gets created? 

===
Assuming that each subsequent table is a single type of data, each of which is linked back to the Curriculum table appropriately, then it does sound like your base is set up fine.  Personally, I wouldn't worry about the deleting of the old records as long as it isn't impeding your workflow

You mention that you'd end up with more than 20 tables at some point, but if each table's a single type of data then that seems fine to me too.  There isn't anything inherently wrong with having a lot of tables; some businesses just have that many types of data