Odtnhj

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.

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.

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