Design users table for single sign in to use across sub domains
<p>We have a site which has sub domains like:</p>
<ol>
<li>example.com <em>-    Main Site</em></li>
<li>food.example.com</li>
<li>fashion.example.com</li>
</ol>
<p>And each domain and sub domain has a different database, like this:</p>
<ol>
<li>exampleDB <em>-    Main DB with Users Table</em></li>
<li>foodDB</li>
<li>fashionDB</li>
</ol>
<p><strong>What have you tried?</strong></p>
<ol>
<li><p>Right now, for single sign in we have planned to redirect users in our Main Site for signing up.</p>
</li>
<li><p>While order processing in sub domains get the UserID from main DB and store in respective sub domain's Orders table.</p>
</li>
<li><p>For reporting purposes we store the <code>UserID</code> without <code>FK constraint</code> in the <code>Orders</code> table since we have separate database.</p>
</li>
</ol>
<p>I can see <a href="https://meta.stackexchange.com/questions/242837/why-does-stack-exchange-use-separate-databases-for-each-network-site">here</a> that stack exchange sites have separate databases, but does it have separate <a href="https://meta.stackexchange.com/questions/103568/how-are-network-site-user-profiles-linked?rq=1">users</a> tables too?</p>
<p><strong>Does StackExchange Network profile is stored in a separate database?</strong></p>
<p>I can see from <a href="https://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede">here</a> that Every site's user table has AccountId of StackExchange Network profile.</p>
<p><strong>An Example:</strong></p>
<ol>
<li><p><a href="https://stackexchange.com/users/7598/nick-craver">Here</a> is Nick Craver Network Profile with ID:7598</p>
</li>
<li><p>His profile in History Site has Account ID Linked with same ID:7598 check <a href="https://data.stackexchange.com/history/query/edit/491023#resultSets" rel="nofollow noreferrer">this</a> query.</p>
</li>
</ol>
<p>I can't see the <code>Accounts</code> table anywhere in <a href="https://data.stackexchange.com/" rel="nofollow noreferrer">data dumbs</a> , So Were is <code>AccountId</code> stored? And how is SSO done in multiple sites using <code>AccountId</code> ?</p>
<p><strong>My Question:</strong> 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 <code>Link Main DB UserID</code> but not FK constraint? Which is the best design for a shopping site?</p>
<p>Any help would be great.</p>