Unable to come up with an efficient structure for database-CodePudding

So I am developing a business web application (ignore). In this app one can create unlimited workspaces for his company/individual. My system is that I have a common database named A. A contains 2 tables, users and workspaces. Users table store user Data like userID, email, password etc. And every time user creates a workspace a record is added to workspaces table and a database is created for the workspace (It is necessary, pls ignore).

Now the problem is how do I list the workspaces the user is when he logs into my web app? I have a table in workspaces recording the number of people in the workspace but I definitely cannot search each database's table to find in which workspaces the user exists. Pls help me come up with a good structure for this.

Sorry if the explanation is a bit long.

CodePudding user response：

Table Users

UserId (PK)
UserName
UserEmail
....

Table Workspaces

WorkspaceId (PK)
WorkspaceName
DatabaseId (FK)
....

Table Databases

DatabaseId (PK)
DatabaseName
....

Table Users_have_Workspaces

UserId (FK)
WorkspaceId (FK)

With the Users_have_Workspaces table, you keep a record of which user has which workspace.

You can obtain the list of workspaces for a user like this:

SELECT WorkspaceId
FROM Users_have_Workspaces
WHERE UserId = <THE USERID OF THE CURRENT USER>

If you do not have the userid, only the username, you can use a sub-query:

SELECT WorkspaceId
FROM Users_have_Workspaces
WHERE UserId = (SELECT UserId FROM Users WHERE UserName = <NAME OF CURRENT USER>)

For the databases that you create (one per workspace), I added a foreign key in table Workspaces to the Databases table. This assumes that there will only be 1 database per workspace. If you need more than 1, you will have to create a Workspaces_have_Databases table similar to the Users_have_Workspaces.