How should you structure stakeholder data?

In short: Name what one continuing record represents before you build the first form, then decide what each later observation records and how it attaches. A person, a survey response and a program enrollment are different things; keep them distinct from the start.

Governing data where it is born means the first form issues the ID everything else hangs on. If that ID belongs to the wrong thing, every later workflow inherits the mistake: an ID per survey response cannot follow a person, and an ID per person with no place for enrollments cannot tell a second course from a follow-up.

So separate two ideas. A record identifies who or what you follow: a person, an organization, a program enrollment. An observation captures something at a point in time: an intake answer, an attendance mark, a mentor note, a follow-up response. One record has many observations, and each observation needs its own date and meaning.

Do not treat a name as proof that two records describe the same person. Names change and are shared. Decide how new records are matched, how an uncertain match is flagged and who corrects a mistaken link. Collect only the identity details the agreed purpose needs.

Which of the four patterns fits your question?

In short: Most teams need one of four shapes: one person over time, several people describing one person, people across several programs, or a network of partner organizations. Pick the one your main question depends on; the others can coexist.

These are a teaching framework, not database designs. A team may follow individuals over time, collect mentor notes about them and report to a network, all at once. The main question decides which shape the first form sets up.

Pattern A: how do you follow one person over time?

In short: Issue the ID at the first form the person fills in, and attach every later observation to it with its own date. This is the shape most programs need, and it is the one the training team uses.

Now test awkward cases. A learner takes a second course a year later: new enrollment, or duplicate person? A learner changes email before follow-up: does the answer still land on the right ID? A learner misses the follow-up: does a report quietly count a "no"? Each of the 15 unknowns should stay unknown.

The main checks are identity, comparability across waves and whether a colleague can reproduce the count. When you get to change over time, following people across waves builds directly on this shape.

Pattern B: what if several people describe one person?

In short: The subject of a response is not always the respondent. Record whom the observation is about, who gave it, in what role and in which round, and decide separately who may see the contributor's identity.

Maria's mentor note is an observation about Maria written by someone else. A leadership program might add peer and manager feedback about the same participant. Each response should attach to the subject's record while keeping the respondent's role, so a report can compare what Maria said about her confidence with what her mentor saw, without the two blending into one average.

An identified confidential response is not an anonymous one. Do not promise anonymity if the role or the event described would reveal the contributor, which is common in small groups. Averages can erase disagreement between raters, so keep the individual responses. Before an assistant can retrieve sensitive notes, settle what the assistant may see.

Pattern C: how do you report across several programs?

In short: One person can join several programs and each program has many people, so the enrollment is a record of its own. Decide whether each report counts distinct people or enrollments, and label it.

Suppose the training team adds a second course and some learners take both. A single "program" field on the person cannot hold two overlapping enrollments with different start dates, exit dates and outcomes. The enrollment connects the person to a program and carries its own dates and status.

This is the standard many-to-many problem, which Microsoft's database guidance solves with a junction table. No SQL needed: the relationship itself has to be recorded and tested.

A program-level participation report may count two enrollments where an organization-level reach report counts one person. Neither total is wrong; label the unit and reconcile the difference. The King Center's customer story describes bringing pre- and post-survey feedback from seven programs into one view. It shows the operational value of organizing feedback across programs; it does not show that every program used identical measures. Agree shared definitions before reading a combined result, which many programs, one picture covers.

Pattern D: what changes for a network of partner organizations?

In short: The record is the partner organization, and each submission belongs to a reporting period and a submitter. Keep a small shared core for comparison and let local questions stay local.

Networks, affiliates and chapters collect recurring returns from partners. Several people may submit for one partner, and one person may work with several partners. The partner, the period, the submitter and the submission are separate things. A document uploaded in the third quarter can still describe the second; its upload date should not overwrite its reporting period.

Access usually follows the partner. In Sopact Sense, each team or site can work in its own folder, where the AI Assistant only sees that folder's data, while the organization owner sees aggregated results across folders. Whatever the tool, test two users from the same partner and one from another: who can view, edit and export which submissions? Then check that a network report tells a missing submission apart from zero activity. For a full workflow built on this shape, see membership and networks.

What should you test before building the first form?

In short: Answer five questions with a few deliberately awkward sample records, and write down the result you expect before you test.

Use a duplicate contact, a repeated submission, an incomplete date and a changed relationship. If the structure fails, revise it before collection scales. Existing data can sometimes be linked later when identifiers and sources are good enough, but reconstruction has limits, and an uncertain match must never be reported as fact.

Can AI help draft the record map?

In short: Yes, as a first draft from your real forms and example files. Review it with the staff who collect and use the data before you build anything.

Remove names, emails and phone numbers from the example files first. The valuable output is a structure your team can explain.