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
password, etc. Meaning: if a user is member of multiple sites, the user has multiple accounts, passwords, etc.
I want to "centralise" 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 "centralised" tables (
user with columns
site), while leaving the "decentralised"
user tables untouched (containing
username but also site specific preferences).
But, how do you maintain integrity between "central" and "decentral" tables when e.g. email address changes? Looking for strategy/ how to approach. Any thoughts?
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 "client" database in the "central" database vs storing
login_id in the "client" database?
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?