Create a single sign-on user table for use in subdomains

Question

We have a website that has subdomains like:

• example.com - Main site
• food.example.com
• fashion.example.com

And each domain and additional domain have a different database, for example:

• exampleDB - main database with user table
• foodDB
• fashionDB

What have you tried?

• Currently, for single sign-on, we plan to redirect users to our Main site for registration.

• While order processing in subdomains receives the user ID from the main database and is stored in the corresponding table of subordinate domains.

• For reporting purposes, we save the UserID without FK constraint in the Orders table, since we have a separate database.

I see here that the stack exchange sites have separate databases, but do they have separate users tables too?

Does the StackExchange network file provide in a separate database?

I see from here that each site user table has an AccountId profile from the StackExchange Network.

Example:

• Here is a Nick Craver network profile with ID: 7598

• His profile on History. The site has an account identifier. Associated with the same ID: 7598 check this request.

I don’t see the Accounts table anywhere data dumbs , So Were is AccountId saved? And how is SSO performed on multiple sites using AccountId ?

My question is: Do we need one user table in the main database or do we need to create separate user tables for the subdomain database and Link Main DB UserID , but not FK limitation? What is the best design for a shopping site?

Any help would be great.

Answer 1

The user identifier you specified is identical only in a separate user table in the same database. This is actually not the identifier of any user in your domain. To identify users in multiple databases, you will need a domain level identifier for each user. A simple way is to add another property (column) with the name UID or something similar to the user class (table), global unique identifier (GUID, see https://msdn.microsoft.com/en-us/library/system .guid (v = vs.110) .aspx ) and use it as a domain level identifier to identify users instead of UserId. Thus, you can freely store information about users in several databases of your domain. So:

• You can only store domain level IDs in each subdomain database and use this identifier to request full user profiles from the main domain database. Pros: Cost less memory. Compromise: in a distributed system, more time is required to request user information from a subdomain, since the information is in the main database.
• You can store full user information in all databases, including main and subtitles. Pro: faster queries. Cons: it costs more memory, and when the user information changes, you will have to synchronize it in all databases.

Answer 2

1] You must have a separate user table in all three databases, because there is no referential integrity of the foreign key. If any database is omitted, then other domains will be executed and executed. Tracing - Database management requires more, but performance will not be difficult.

Alternatively, you can create the following database plan.

1] Store one database for all three domains (one primary domain and two subdomains)

2] Create one user table that has two status flags for food_order, fashion_order

3] Create an order table for food and fashion separately

4] There are three possibilities that the user orders i) Food or ii) Fashion or iii) Food / Fashion like.

5] Save the update status flag as ordered by either Food, Fashion or Food / Fashion.

The trade-off here is that if your database goes down, then all three of your sites go down. So keep a very good database crash recovery model.

Answer 3

Some options:

• Save users only in the main database. Then...

  • use an encrypted cookie to store all the user information you care about. This cookie will be readable by all other subdomains.
  • OR just save user_id in a cookie and use the channel feedback through the API to get user information from the main site.
  • OR use one of many existing fairly standard protocols for exchanging user information to get information about users on other sites, where you can save them in the current session repository. Some examples include CAS and OAuth.

• Save user information in all databases. You usually get information in other databases using one of ...

  • regular job synchronization.
  • reverse link communication when a user logs in
  • one of the fairly standard protocols for exchanging user information (as indicated above)

With option 1, your main database will work on all other sites. With option 2, non-core databases are heavily cached copies, and you need to deal with cache invalidation.

Which option you choose will mainly depend on your specific business needs.

Answer 4

I think what you started in your question, this is your best choice.

It is best to have one user table and three configuration tables. One user table will be associated with single sign-on functionality, which will check users, their general profile and account information. Each service / website is not related to a domain that uses a federated SSO that validates user cookies. That is, pretend that everything was the same, except that you use google web federation for SSO. The data stored in google does not really bother you (except, maybe there are fields that you do, maybe a username, etc.). No matter which domain you are in, the first thing you do is call the SSO, and see if the user is already registered, if they do not direct them there, and when they are done, they will be redirected back to your subdomain.

If they are logged in, then do not redirect, but you get a jwt token that will allow you to convert the stored user identifiers (or UIDs) into actual information.

Say the registered user is buying something from fashion.example.com. You can add a row to the order table that has a column named "userId". If this site wants to publish "user X ordered Y" on the first page, after receiving the order from the table, you must call the single sign-on service to return jwt with user information using the SSO token. If for some reason you wanted to save a configuration that is different from the subdomain, then this subdomain will create its own "users" or "configuration" table, and the key will be associated with the UID in the single sign-on service. Regardless of whether you have a unique PK (from FX to UID) or if PK is a UID.

This will allow you to completely separate the domains from each other. And let your subdomain decide if they need a "users" table or not.

TL; DR. You really have 4 domains, not 3, consider it as 1 SSO authentication domain and 3 sites. SSO will have an auth table, and each site domain may or may not have its own user table, if necessary. This user table will have an FK in the SSO table without the need for forced FX restrictions. (The FK restriction, if it can be enforced in some way, would actually be bad because you would not want to use the single sign-on service to know about the subdomains when the user account should be deleted, the fact that they are all different, the database is already awesome.)