Attendance Return with 38 Duplicate Students

The single table mixes three different things: facts about a student, facts about an attendance event, and the meaning of a code. That mixing causes most of the listed problems.

Redesign:

• Student(StudentID PK, FirstName, LastName, DOB, WhanauGroup)
• Attendance(AttendanceID PK, StudentID FK, Date (Date/Time), Code FK, MinutesLate, EnteredBy)
• Code(Code PK, Description, CountsAsPresent Y/N) - collapses 'L', 'late', 'Late-bus' and 'LATE' into one row
• Index on Attendance(StudentID, Date)

What each change fixes: storing Date as a real date and entering it through a date picker removes the 12/03 versus 03/12 ambiguity and lets term and percentage calculations be done in SQL instead of in the officer's head. The code lookup table removes free-text variants that currently break every count. The index plus narrower rows cuts the 45-second query to something usable, and moves the caregiver phone out of 61,400 repeated rows into one student record.

Important limit: splitting the table does not by itself remove the 38 duplicates. A primary key stops new duplicates only if StudentID is the sole identifier used; the existing 38 still need a matching and merge step. Structure change and data cleaning are separate jobs and the plan must say so.

Apply least privilege through roles, not individual grants:

• Attendance officer: read/write attendance, read student details, no schema changes.
• Teacher aide: insert attendance codes only for students in their whānau group - enforced by a per-group query or view, not by trust.
• Principal and board: read-only reports.
• One nominated person holds admin rights, and that account is not used for daily entry.
• Revoke the aide's admin rights today. The risk is not malice but an accidental table delete or an export left on a desktop.

Privacy Act 2020 reasoning: attendance records are personal information about identifiable students. IPP 5 requires reasonable security safeguards against loss, access and misuse - shared admin accounts and unrestricted CSV exports fail that test. IPP 11 limits disclosure, so exports for the return go only to the Ministry, through an approved channel, and are deleted afterwards. Only collect what is needed: the caregiver phone number belongs once in the student record, not duplicated in every attendance row.

Record the access change and report the exposure to the school's privacy officer - it is a real incident, not just a design preference.

Cleaning must be reversible and evidenced.

1. Freeze a copy. attendance_raw stays untouched; all work happens in a staging table. Any mistake is then recoverable and an audit trail survives.
2. Normalise formats. Parse dates with an explicit rule: if the first number is greater than 12 it must be the day; otherwise treat ambiguous values as DD/MM and flag them for checking against the source system. Map codes through the lookup table.
3. Match duplicates on exact DOB plus a similarity check on name and caregiver. Expect 38 candidates, but review every one by hand - twins and re-enrolments look identical to a fuzzy match.
4. Merge, don't delete. Keep the lowest StudentID as the survivor, repoint attendance rows to it, and record the old ID in an Alias column. Deleting loses attendance history and can break the roll return.
5. Verify. Student count after merge equals 1,150; total attendance rows unchanged; per-student percentages recalculated and spot-checked against five known students whose attendance the officer can confirm from class records.
6. Prevent recurrence. Re-run the same validation rules on every import so duplicates and bad dates are rejected at the door.