1

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 "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 username, password, etc; login_has_site linking user with 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?

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 "client" database in the "central" database vs storing login_id in the "client" 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?

  • First, decide on how to deal with the odd cases -- same user name but different email address; or same email address but different user name; etc. You may discover a dozen different reasons, some excusable, some not, for the descrepancies. – Rick James Nov 6 '18 at 22:36
0

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.

  • Thanks Dan, helpful. Have added some additional questions. – Lennart Nov 7 '18 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.