easySPEAK — Database Design: Answers to the Seven Questions

1 · How many tables?

163. Of those, 71 are Toastmasters tables (phpbb_tm_* — clubs, meetings, roles, speeches, officers…) and 92 come from the phpBB2 forum platform and its add-ons (forums, users, sessions, albums, knowledge base, security tooling). So roughly 4 in 10 tables are "ours"; the rest are inherited platform.

Recommendation: no table consolidation before the instance merge — 163 is not a problem in itself, and restructuring mid-migration multiplies risk. What matters is freezing a convention for new tables now (see Q3/Q4) so the count of non-conforming tables stops growing.

Check it yourself

grep -c "CREATE TABLE" database/easyspeak_v2.33_structure.sql        # → 163
grep -c 'CREATE TABLE `phpbb_tm_' database/easyspeak_v2.33_structure.sql   # → 71

2 · Third normal form or Boyce–Codd normal form?

In plain English: both are rules for "don't store the same fact twice." 3NF is the practical industry standard; BCNF is a slightly stricter academic variant that only differs in rare edge cases involving overlapping candidate keys. For a schema like this one, the difference is almost never observable in practice.

Recommendation: target 3NF for all new tables; do not retrofit BCNF anywhere. The schema is broadly 3NF already, with a handful of deliberate denormalisations that should be documented as such rather than "fixed": phpbb_tm_users carries cached columns like last_attended, last_role, last_spoke (derivable from attendance/assignment history, but cached for speed and maintained by stored procedures on meeting close). Removing them would slow every member list and chart for zero benefit. The pragmatic rule: 3NF by default, denormalise only for measured performance, and write down every exception.

Check it yourself

The cached columns and their updater: database/easyspeak_v2.33_structure.sql (tm_users table) and includes/dbaccess/upd_sprocs.php:363 (last_attended_upd()), :448 (last_role_spoke_upd()).

3 · Which collation?

Collation = the rules for comparing and sorting text (is "a" = "A"? where does "é" sort?). Today the database uses four different collations across its 163 tables:

Collation today	Tables	Meaning
`utf8mb3_general_ci`	143	Deprecated 3-byte UTF-8, case-insensitive
`utf8mb4_general_ci`	13	All the newer tables (OAuth, evaluations, API permissions…)
`utf8mb3_bin`	5	Exact/binary comparison (timezones, payment methods, email validation…)
`latin1_swedish_ci`	2	Legacy payment tables (`tm_conf_payments`, `tm_es_payments`)

Recommendation: standardise everything on utf8mb4_unicode_ci, with two deliberate exceptions kept binary (tm_timezones — IANA zone names are case-sensitive keys; tm_pay_methods — credential fields) as utf8mb4_bin. Why this one and not the alternatives:

Not utf8mb4_0900_ai_ci (MySQL's modern default): it does not exist on MariaDB, and the dev environment is MariaDB 10.6 — production is very likely MariaDB too (verify first, see Challenges). Choosing it would make the migration undeployable.
Not utf8mb4_general_ci: linguistically cruder (mis-sorts many accented and non-Latin characters); and since only 13 tables use it, standardising those 13 onto unicode_ci is far cheaper than standardising 143 the other way.

Check it yourself

grep -o "COLLATE=[a-z0-9_]*" database/easyspeak_v2.33_structure.sql | sort | uniq -c
#   2 latin1_swedish_ci · 5 utf8mb3_bin · 143 utf8mb3_general_ci · 13 utf8mb4_general_ci
grep mariadb tooling/easyspeak-container/docker-compose.yml     # dev = MariaDB 10.6

4 · Common character set — UTF-8?

Yes — but the important detail is which UTF-8. MySQL's utf8mb3 (what 148 of our tables use) is a deprecated 3-byte version that cannot store emoji or many modern characters. Real UTF-8 is utf8mb4.

The trap nobody sees: even the 13 tables already on utf8mb4 can't receive 4-byte characters today, because the application opens every connection with SET NAMES UTF8 (= utf8mb3) at website/includes/db.php:68. The connection, not just the tables, has to be upgraded — and only after all tables are converted, or 4-byte data will start flowing into 3-byte tables and corrupt.

Recommendation: utf8mb4 everywhere, migrated in phases — (0) verify production server version & take a verified backup, and freeze the convention so new patches stop adding utf8mb3 tables (PATCH_v2.34 added 4 more); (1) fix 8 config tables whose 255-character primary keys would exceed the old index size limit (shrink to 191 — values are short config keys); (2) convert tables in batches, big phpBB core tables last in a maintenance window; (3) flip db.php to utf8mb4; (4) repeat per instance. Good news that makes this cheaper than it looks: zero FULLTEXT indexes and zero charset references in any of the 119 stored procedures — the proc layer migrates for free.

Check it yourself

grep -n "SET NAMES" website/includes/db.php                      # line 68: SET NAMES UTF8
grep -c "FULLTEXT" database/easyspeak_v2.33_structure.sql        # → 0
grep -ci "utf8" database/storedprocedures/all_stored_procedures.sql   # → 0

5 · Duplicate usernames across instances

The same username can exist independently on each of the 3 instances, possibly belonging to different people — or to the same person registered twice. Two facts shape the answer:

You can't even reliably compare usernames across instances until they share one collation — "PaulC" vs "paulc" is equal under case-insensitive collations and different under binary ones. So Q3/Q4 are prerequisites, not separate workstreams.
Usernames identify people badly; verified email addresses identify them well, and the schema already tracks email validation (user_email_validation).

Recommendation: after collation convergence, dedup on verified email as the primary match key: same verified email on two instances = same person → merge into one account. Genuine username collisions between different people are resolved by renaming with an instance suffix (or inviting the newer account to pick a new name), with an old→new mapping table kept so nothing breaks. This also naturally pairs with adopting a username convention going forward (e.g. surname+initial, as discussed in the group) so the problem shrinks over time instead of growing.

Check it yourself

Run on any two instances and compare: SELECT username, user_email FROM phpbb_users WHERE user_email IN (SELECT user_email FROM …) — the overlap count is the size of the real problem, measurable before committing to a strategy.

6 · Users who are members in multiple easySPEAK instances

This is the same person as Q5 seen from the other side — and the schema is already built for the answer: one user account can hold many club memberships. Within a single instance, phpbb_users (one row per person) links to phpbb_tm_users (one row per person per club). Multi-club membership works today — there's even a "member of which clubs" page (disclose.php).

Recommendation: at merge time, collapse each person (matched per Q5) to one phpbb_users row keeping all their phpbb_tm_users club rows — memberships, speech history and Pathways progress all key off the user id, so a single id-mapping pass carries everything. The payoff is user-visible, not just tidiness: one login everywhere, one profile, and cross-club views become possible — e.g. the speech-slot fairness "traffic lights" on the matrix page can then use a member's overall speaking recency rather than "never spoke here" while they speak weekly at another club.

Check it yourself

Table relationship: phpbb_tm_users has (user_id, club_id) — see database/easyspeak_v2.33_structure.sql; multi-club page: website/disclose.php.

7 · One unique easySPEAK URL

Today there are multiple domains (tmclub.eu, toastmasterclub.org, …) pointing at different instances, and the platform already contains a routing layer (phpbb_tm_redirect sends districts to the right instance — documented in docs/instance_redirection.md and docs/hosting.md). The pain is real and current: log in on one domain and you are not logged in on another, because the session cookie belongs to a single domain — exactly what tripped a Cambridge member on 9 July trying a different URL.

Recommendation: pick one canonical domain and redirect the others to it rather than switching them off — people have years of bookmarks and favourites, so the legacy domains keep working as signposts (redirecting to the single login) and get phased out gradually once traffic on them dies away. On which domain: the easySPEAK brand beats toastmasterclub — it's the product's name, it isn't tied to one club community's phrasing, and the project infrastructure (GitLab) already lives on easy-speak.org. (If this were a commercial product the answer would simply be easyspeak.com.) Technical checklist for the cutover: session cookies issued only on the canonical domain; the existing phpbb_tm_redirect layer becomes the temporary bridge, then retires; email templates and calendar/webcal links regenerated to the canonical host.

Check it yourself

docs/instance_redirection.md, docs/hosting.md, table phpbb_tm_redirect; and the login-across-domains behaviour is reproducible in any browser today.

Challenges still to be overcome (open, honestly)

#	Challenge	What resolves it
1	Production server version unverified — every recommendation above assumes prod ≈ dev (MariaDB 10.6)	One command on prod: `SELECT VERSION(), @@character_set_server, @@innodb_default_row_format;` — do this before anything else
2	Downtime for big-table conversion — phpBB core tables (`posts_text`, `users`, `topics`) are the largest	Convert last, in a maintenance window; or `pt-online-schema-change` if zero-downtime matters
3	In-flight patches keep adding utf8mb3 tables (v2.34 added 4)	Convention freeze now: one line in AGENTS.md — new tables are `CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
4	The two latin1 payment tables have odd column-level overrides	Data is ASCII (transaction ids, currency codes) — standard CONVERT handles it; verify with a checksum before/after
5	`user_email_validation` is binary-collated — emails are case-insensitive by spec, so this looks accidental	One-line decision from Rhys: keep binary or fold into unicode_ci
6	How many real duplicate people exist across instances is unknown	Measure first (Q5 email-overlap query) — the strategy should follow the number, not precede it
7	Domain ownership & hosting accounts — instances sit on two hosting accounts with different domain registrations	Inventory who owns/renews what (`docs/hosting.md` is the start); a team decision, not a technical one

Additions welcome — this list is meant to grow. Kit and others will know constraints that aren't visible from the code.