Mar 29, 2020 06:03 AM
I am trying to track the accreditation process and had thought to set each table to include the various components of the levels of accreditation (beginner, intermediate, advanced). However I need the primary field which is the list of personnel names to copy across each table without needing a linked field that would require updating when entering new personnel.
Is that possible?
Mar 29, 2020 08:50 AM
Welcome to the community, @Nicky_Engert! :grinning_face_with_big_eyes: From your description, it sounds like you’re using multiple tables to track information about the same people. Is that correct? If so, I strongly suggest rethinking your design to only use a single table. To divide the different stages of the accreditation process, you would use multiple views that show different field combinations for those same contact records.
As an example, I teach online classes in an animation course, and have a base where I track everything about that course: students, course schedule, extra credit opportunities, etc. I have a single
[Students] table where all student information is tracked. In addition to core info about each student (name, location, website profile link, etc.), I also have fields that help me with calculating grades for certain assignments, recording extra credit scores, tracking absences, etc. Altogether, I have 80 fields in that one table, but I don’t need to see them all at once. I’ve created views (17 total) to only show the necessary fields for a given activity. So when I’m grading their Week 4 assignments, I move to the “Grading: Week 4” view, which only shows the six fields I need at that time.
In the end, a single table ends up containing a lot of info, but it’s actually much easier to track by putting all necessary fields into that single table, and using views to manage what’s visible at a given time.
Mar 29, 2020 02:37 PM
Hi Justin, thanks for your response. I suspected as much but was really hoping I could separate into separate tables.
Mar 30, 2020 03:18 PM
Happy to help! I do recommend giving the single table system a try. Considering that you posted in the “Base design” category, I should point out that using a single table to contain all data related to a specific category, like contacts, is definitely better database design than splitting across multiple tables. A key element of database design is only having a single source for each type of information you’re storing. Keeping the same information in multiple places is less efficient, and can lead to confusion.