Need help in designing a database schema for a SaaS application

Question

I am a developer and have never worked on DB before (designing a DB). I am designing a database for an employee management system which is a Node.js + Express application using MySQL as its DB.

I already have the required tables, columns sorted out but there are still few unknowns I am dealing with. This is my plan so far and I need your input on it.

1. The end users using this application will be small - mid size companies. The companies won't be sharing the tables in the database. So if there is a table named EmployeeCases I plan to create a new EmployeeCases table for each existing company or a new one who signs up for this application. I am planning to name the table as EmployeeCases_989809890 , where "989809890" will be the company id (or customer id). So if we have 3-4 companies who signed up for us, then all the tables (at least the ones which a company uses) will be recreated and named as TableName_CompanyId. My questions, is this a good way to go? Is there a better way?

2. All the employee's data is held by the Employee table, including their login and password. Now each Employee table in DB will be named as Employee_CompanyId (as per my plan above). My question is, when an employee logs in, how will I know which Employee table to query to? Or should I remove the login from the Employee table and create a universal Users table where all the employees will be stored? The Users table will also have the CompanyId as one of its column and I will read the CompanyId from there which will be used to query other tables.

Any reference, website or blogs on this type of design will be appreciated.

Thanks.

Answer 1

I don't recommend this approach, I think you should either:

A) Put all the information in the same tables and have a companyId column to sort them out

OR

B) Have separate databases for each company and use the appropriate database using the code.

The thing is, with your approach, you'll have a hard time maintaining your application if you have multiple copies of the same table with different names. If you decide to add a column to one of the tables, for instance, you will have to write as many SQL scripts as you have table instances. You'll also have a bad time with all of your unique identifiers.

Here are some advantages/disadvantages of each design:

A) Put all the information in the same tables and have a compagnyId column to sort them out

Advantages:

• Simplest
• Allow usage of foreign key / constraints
• Great for cross / client data extraction

Disadvantages:

• Not portable (a client can't just leave with his/her data)
• Can be perceived as less secure (I guess you can make the case both ways)
• More likely to have huge tables
• Does not scale very well

B) Have separate databases for each company and use the appropriate database using the code.

Advantages:

• Portable
• Can be perceived as more secure

Disadvantages:

• Needs more discipline to keep track of all the databases
• Needs a good segregation of what's part of your HUB (Your application that tracks which client access which database) and that's part of your client's database.
• You need a login page by company (or have your clients specify the company in a field)

An example of an application that uses this "two-step login" is Slack, when you sign-in you first enter your team domain THEN your user credentials.

I think Google Apps for Work as the same approach. Also, I think most CRM I worked with has a separate database for their clients.

Lastly, I'd like to direct you to this other question on stackoverflow that links to an interesting example.

Answer 2

You shouldn't split your tables just because companies won't share their information. Instead, you should have a companyId column in each table and access to the relevant data for each query. This should be implemented in your backend