Any option other than a separate database per user?

Question

I'm developing an application which users register their company and stores all their information;

• Company

  tax_registration_no, name, address, etc
  

• products

  product_code, description, price, etc.
  

• departments

  department_code, name. 
  

• employees

  employee_id, username, password, address
  

• individual employee permissions

  employee_can_access_till, employee_can_provide_discount, etc.
  

• customer

  name, address, orders
  

Each company that registers will be given a template spreadsheet file in which they can copy their data in and send it back to my client for storage.

My client wants a separate database per company to be generated when the file is uploaded to the site, but I'm not convinced this is a smart way to go (and it sounds like a maintenance nightmare). How would things like employee authentication work if I've multiple databases to check?

The data will be stored internally on the device so it will only be a one time sync to the server to get the data, and then again whenever its updated.

My initial idea was just to store the spreadsheet on the server and parse and send that out via JSON to the app when its needed. this way it's just a single file that can be downloaded, amended and re-uploaded. with only storing the employees, and perhaps companies, in a single database.

Any advice on how to go about this?

Answer 1

You definitely do not need a separate database per user. It is hard to tell exactly what you mean by "user", but it sounds like a company.

Instead, simply include companyid in all the appropriate tables. From your list, that would seem to be all except the individual_employee_permissions table (you get the company from the join to employees).

This is the "normal" approach to designing such a database and it has lots of advantages. You can easily query for things that happen across companies -- How many employees are in each company? How many customers? Which employees have admin privileges?

Another advantage is on the maintenance side. If you need to fix the system or add functionality, it is much easier to do on a single database than on multiple databases. Similarly, backing up and restoring the system is a much simpler process on one database.

There are a couple of reasons why you would store companies in separate databases. These reasons override the convenience described above. The first is if each system is a customized system for the company. Then functionality could be added for one company independently of others.

A second reason is if security restrictions mandate that data not be stored with other companies' data. This sometimes happens, less often for a good reason. But such a requirement could mandate separate databases and even separate servers for each company.