Database Normalization vs dependencies

Question

I am developing 3-4 interdependent programs. Call them foo bar baz and auth. I want them to be independent of each other. Imagine if I were to license out each program to other companies. Some companies may want foo and bar, others may just want baz, etc. It also seems like good practice to keep auth independent as well.

Context: auth is taking care of authentication for all of the systems. The main users table in auth has user_id, email, password, first, last

foo has a users table as well which has specific fields for the application: user_id, role_id, etc.

Each system has its own database. In the past I have created a foreign key from each application to the auth db. I removed update permissions from the other dbs but granted select access to certain relevant fields. This seems like a bad solution because it creates a tight dependency, but it allowed me to normalize the db so that I didn't have to store the users name and email in the foo, bar, or baz databases.

Would it be better to store the information in all of the databases? Or would it be better to store the Auth Id in foo bar and baz and use an api to get the user info using the authId?

Similarly, I might have customers in all 3 systems. Sure it seems bad to create a dependency into an auth db, but what about the customer dbs across all 3 of them?

Or is the best solution to have one central db. One users table.

Other Suggestions?

Answer 1

If you have 4 independant services, then they have to independant. No data should be shared in common DBs or similar, as then you simply make them dependant!

Now while its OK to share a single DB in production, the services should use their own schemas as to DB is just there as a common container, similar to how a single Linux server can run all 4 services.

Each service should have its own API, and this is the only way to get data in and out of each. So your auth service will store users and perform authentication, but will return some token to identify a user. This token is what the other services use to remember which user is which - but otherwise they should have no knowledge of user authentication at all.

The easiest way to think of implementing these is to think what would you do if you decided to use a 3rd party service instead of the ones you're writing - if you used StackExchange's OpenID service for users instead of your own. If you can replace that into your architecture then you have a good, independent design.

Answer 2

Your example is a bit abstract, but let me run through my thoughts.

Option A : In my experience this is Always Bad regardless of your reasons. sure DBs like MSSQL can have friend databases etc, but if the data is linked, it should be in the same DB

Option B : Not sure whats going on in here, you are adding an API which queries all DBs and brings the results together? This is Better, but if you have Apps on top of that API then its part of the datalayer for them and they really only use one of the DBs, so why have they got the others in thier data layer?

Option C : Well this works, but its not good to have one big database with unrelated data in it. You want the programs to be independent of each other right? this dosnt help with that.

Option D

My suggestion is to abstract the authentication away form the concept of userId. your Auth App should be concerned with users and roles (or promises etc if your are going all modern) this data should exist in the Auth DB

Each App needs to have a userId to group data by user, with some related data to make the auth call and identify the user to humans. this would be userId (unique to the app), real name, authusername, auth service to use. This data should exist on the apps DB

The databases should have no connection between them, conceptually you should be able to use facebook as your auth service rather that your Auth App

You may need a Further User linking information if you want to relate a foo user with a bar user

Answer 3

I would recommend implementing Single Sign On (SSO), for example with LDAP. Your larger customers probably would have benefited from it as well, as they would be able to replace your built in auth app with their own existing user database. For your smaller customers that don't support SSO, you can simplify deployment by shipping your app with a default auth app.