A client of mine owns a company that is starting to grow rapidly. He sells several custom-built online web marketing tools. Each tool contains its own database & each database has it's own tables for client accounts, usernames, etc. The problem is, many clients subscribe to multiple tools of his, so managing their usernames & accounts across multiple databases has become a mess, so I'd like to setup a separate Client database, to serve as a central client repository. I have some thoughts in mind, but was curious if there was a more universal structure recommended or any thoughts on how to go about this.
At least to start off, I was thinking of the following tables within this Client database:
dbo.Client (client name, address, contacts)
dbo.Service (names of each web marketing tool)
dbo.ClientService (associated entity table of the services associated with each client)
dbo.User (email & password logins. Links to Client table. Each client can have multiple users)
So now, when a user logs into any online web tool, we would query against this new Client database's "User" table for the login & password, as well as the "Service" & "ClientService" tables to ensure this client has access to this particular tool.
Is something like this fine or is there a more universal standard I should be following, to ensure scalability?
Thanks