3

I'm working on really nice project in ASP.NET MVC, where comapnies can generate some pdf documents, save some data etc. I have a piece of project already. [no important]

Programist will add Company with one AdminUser, AdminUser[Boss of company or smth like that] will add next users with roles - 'worker' etc.

So there is problem. I have >10 tables, one company can easly create 50 rows in each table by one month. So what is better add to few tables "CompanyId" and filtr everything, or create new database with 10 tables for each company?

For start i want hmm ~20 companies. But next for example 100-1000 companies.

I think that second solution is better, but how to do it? At last can you direct me to good books/articles about it?

JJJ
  • 51
  • 5
  • https://stackoverflow.com/questions/34493998/multi-tenancy-or-multi-instance may be of interest. – mjwills Sep 16 '17 at 12:04

1 Answers1

3

Under almost all circumstances, you want to store the data in a single set of 10 tables. A load of 50 rows/month growth is really nothing in a database. A load of 50*10 rows/month is really nothing.

Why do you want them in a single set of tables?

  • You can readily query across all the tables to see what is happening across your business.
  • You can set up the tables once and forget about them.
  • You can more easily update the software and install fixes. You only need to do this once, not once per client.
  • You don't have the danger of different clients getting out of synch.
  • Tables are more efficient when they have lots of data rather than when they have little data.

There are circumstances where you would want a separate database for each client, with their own set of tables:

  • Security restrictions require that you physically separate the data.
  • Each client has a bespoke application, so you expect different clients to be using different versions.
  • Clients have to access different versions of the database software for some reason.
  • You need to be sure that you can separate clients for performance reasons.

There are no doubt more reasons on both sides. These are just some I can think of quickly.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Okay. But now 10*50 rows *20 companies( on future *300) per month some companies are big so they have 200 rows*10 per month, so this is a lot of rows. Ofc i have to filtr only 3 tables rest od them are associated. Documents which have more than 9 moths will be zipped, once a month(as pdf) and deleted from db. I want program that can filtr and response pretty fast. If i have so much rows that will be fast anyway? – JJJ Sep 16 '17 at 12:28
  • 1
    @Jackob . . . If you were talking per second or per minute, you might have a concern. Tens of thousands of rows per *month* is not an issue. – Gordon Linoff Sep 16 '17 at 13:32