-- We know all the X.500s of students we expect to have.
CREATE TABLE IF NOT EXISTS student_x500s
  ( x500 TEXT NOT NULL
  , UNIQUE(x500)
  );

-- In the common case, we can just map users to their X.500s.
CREATE TABLE IF NOT EXISTS uids_to_x500s_clean
  ( uid  INTEGER NOT NULL
  , x500 TEXT    NOT NULL
  , FOREIGN KEY(x500) REFERENCES student_x500s(x500)
  , UNIQUE(uid)
  , UNIQUE(x500)
  );

-- ...until we see someone change X.500, or try to take someone else's X.500,
-- or use an unenrolled student's X.500.
--
-- These show up as a violation of one of the constraints in
-- uids_to_x500s_clean. In that case, we still add the failing tuple to
-- all_seen_uids_to_x500s. This lets us get a list of "suspicious tuples" by
-- subtracting uids_to_x500s_clean from all_seen_uids_to_x500s.
CREATE TABLE IF NOT EXISTS all_seen_uids_to_x500s
  ( uid  INTEGER NOT NULL
  , x500 TEXT    NOT NULL
  , UNIQUE(uid, x500)
  );