"Centralising" a "decentral" design I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of multiple sites, the user has multiple accounts, passwords, etc.
I want to &quot;centralise&quot; the user tables, so users can log in once for all sites they are member of.
The username is the email address; which is used on a lot of places in the app.
Ideally I'd use a few &quot;centralised&quot; tables (user with columns username, password, etc; login_has_site linking user with site), while leaving the &quot;decentralised&quot; user tables untouched (containing username but also site specific preferences).
But, how do you maintain integrity between &quot;central&quot; and &quot;decentral&quot; tables when e.g. email address changes? Looking for strategy/ how to approach. Any thoughts?
Additional information
Basic schema
CREATE TABLE `login` (
  `login_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` text NOT NULL,
  `salt` text NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `login_has_site` (
  `login_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In each client database:
CREATE TABLE `user` (
  `login_id` int(11) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  -- other fields  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What would be the advantage of recording the user_id in the &quot;client&quot; database in the &quot;central&quot; database vs storing login_id in the &quot;client&quot; database?
Also: the username column is referenced in a lot of places in the codebase. Would it be wise to make it a key in the central database and use e.g. on update cascade to maintain integrity, while not having to rewrite all code?
I suggest creating a single common database. In this common database create your user table.

Instead of a login_has_site field, have a table of access that can be joined:


primary user id (of common table)
client name (other database)
user id in client db


After you have have that you can start to deprecate the fields in the user table across all but the common database's user table.