The Shared Spreadsheet That Became the Database
A small sports club asks a student to build a membership system. The club currently keeps everything in one spreadsheet: one row per member, with columns for name, address, phone, team name, coach name, coach phone, and a comma-separated list of events the member has entered. There are 340 members, 18 teams, and 6 events. The club wants to email all members of a team, list everyone entered in an event, and update a coach's phone once rather than on every row. The student proposes keeping the spreadsheet but adding filters and formulas. A mentor suggests instead designing a relational database with separate tables for members, teams, coaches, and events, plus a linking table for entries. The club is worried the database is 'more work' and harder for volunteers to use.
1. Identify the data anomalies in the spreadsheet design and explain how the proposed relational design removes them.
Read the worked answer
Anomalies in the spreadsheet:
- Update anomaly: a coach's phone appears on every member row of that team; changing it once requires editing many rows and risks inconsistency.
- Insertion anomaly: a new team or coach cannot be recorded until a member joins it, because rows are member-centric.
- Deletion anomaly: removing the last member of a team deletes the only record of that team and coach.
- Multi-valued field: the comma-separated event list cannot be reliably queried, sorted, or constrained.
How the relational design helps: Separate tables (members, teams, coaches, events) store each fact once. A linking table (entries) maps members to events many-to-many. Coach phone lives once in the coaches table, referenced by teams via a foreign key. Queries like 'all members of a team' or 'all entrants in an event' become joins rather than text parsing.
Judgement: The anomalies are structural, not fixable by filters and formulas.
2. The club worries the database is more work and harder for volunteers. Recommend a design and rollout that addresses this concern.
Read the worked answer
Recommendation: Build the relational database as the single source of truth, but provide a simple form-based front end (or a filtered view) so volunteers do not write SQL. Keep the spreadsheet only as an export for printing or sharing.
Rollout:
- Define the schema and keys (member ID, team ID, coach ID, event ID, entry linking table).
- Migrate the 340 rows, splitting the comma-separated event lists into entry rows; validate counts (6 events, 18 teams).
- Provide saved queries/views for the three stated needs: team email list, event entrant list, coach update.
- Train volunteers on the forms; keep a read-only export for reassurance.
Trade-offs: Up-front effort is higher, but recurring effort drops because updates happen once. Data integrity constraints (e.g. a member cannot enter an event twice) prevent errors volunteers would otherwise introduce. The usability concern is real and must be met with a front end, not dismissed.