Design users table for single sign in to use across sub domains

Question

We have a site which has sub domains like:

1. example.com - Main Site
2. food.example.com
3. fashion.example.com

And each domain and sub domain has a different database, like this:

1. exampleDB - Main DB with Users Table
2. foodDB
3. fashionDB

What have you tried?

1. Right now, for single sign in we have planned to redirect users in our Main Site for signing up.

2. While order processing in sub domains get the UserID from main DB and store in respective sub domain's Orders table.

3. For reporting purposes we store the UserID without FK constraint in the Orders table since we have separate database.

I can see here that stack exchange sites have separate databases, but does it have separate users tables too?

Does StackExchange Network profile is stored in a separate database?

I can see from here that Every site's user table has AccountId of StackExchange Network profile.

An Example:

1. Here is Nick Craver Network Profile with ID:7598

2. His profile in History Site has Account ID Linked with same ID:7598 check this query.

I can't see the Accounts table anywhere in data dumbs , So Were is AccountId stored? And how is SSO done in multiple sites using AccountId ?

My Question: Do we need a single user table in Main DB or we have to create separate user tables for the sub domain's database and Link Main DB UserID but not FK constraint? Which is the best design for a shopping site?

Any help would be great.

Answer 1

The UserID you mentioned is only identical in a specific Users table in a single DB. It's actually not an identifier of any user in your domain. To identify users across multiple databases, you will need a domain-level ID for every user. A simple way is to add one more property (column) named UID or something like that to User class (table), of Global Unique Id (GUID, see https://msdn.microsoft.com/en-us/library/system.guid(v=vs.110).aspx) and use it as domain-level ID to identify users instead of UserId. This way you can freely store user information in multiple databases in your domain. So:

• You can hold only domain-level ID in every sub-domain's database and use that ID to query full user profiles from the main domain DB. Pros: costs less memory. Trade-off: in a distributed system, it takes longer to query user information from sub-domain because the information residing in main DB.
• You can hold full user information in all DB, including main's and subs'. Pro: faster queries. Cons: costs more memory and when a user information changes, you will have to synchronize it across all databases.