0

We developing service were for each client user can make appointment. Is there strategy - table in database (MS SQL SERVER) for each client will be proper?

We think such strategy simplifies and exclude complex table locking.

Alexandr
  • 1,452
  • 2
  • 20
  • 42

1 Answers1

1

It sounds like you are trying to build a multi-tenant application. I would not recommend one table per client. Rather, I would recommend one of two solutions:

  1. Integrate all client's (tenants) data into the same tables and segregate by an identifier. In other words, every top-level table would have a foreign key called "ClientId" or "TenantId" which would identify and separate each client's data. The downside to this approach is that the database gets much larger faster and large databases are more complicated to manage that smaller ones. In addition, there is a risk that a developer builds a query that forgets to filter on ClientId and one client sees another client's data.

  2. One database per client. A more extreme solution but useful if clients will host their own applications, if there is great disparity in the sizes of each client's data, or if client's want absolute assurance of data separation.

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • I think situation with foreign key is not applicable because of there is limit up to 256 from one table. In other side we should store one table with data for each client. Because in case of parallel working many users(100-1000) can access to one table; in case one table per client it would be 1-10 users. – Alexandr Apr 21 '11 at 08:45
  • @Alexandr - Huh? There would be *one* foreign key column in each of the top level tables that would reference a table of clients. – Thomas Apr 21 '11 at 16:19
  • @Alexandr - I am definitely not suggesting adding a FK column per client. That makes no sense and is not normalized and is unnecessary. Instead, each top-level table would include data for *all* clients where you differentiate one client's data from another by a single column which is a FK to a table of clients. – Thomas Apr 21 '11 at 16:26
  • @Alexandr - For example, suppose our system is storing business partners. We would have our BusinessPartners table with the attributes applicable to that entity (name, address, phone etc) and another column called "TenantId" or "ClientId" which would let us differentiate one client's business partners from another. – Thomas Apr 21 '11 at 16:26
  • OK, It's that we can store clients, but I mean structure when we have many data for all clients. Yes we can store it in one table, but we will face up with many locking/blocking side effects. Instead of we want to split data for each client per table and store references to tables in separate table. For examle, columns [pkClientID, TableName] - data - [1, dbo.tblClient_1], [2, dbo.tblClient_2],...[n, dbo.Client_N] instead of having table dbo.Client_All data. – Alexandr Apr 22 '11 at 09:01
  • @Alexandr - Unless you have code opening table locks, you shouldn't have locking problems. What happens when you have two tables per client? How about 10? A typical schema is not restricted to a single table. IMO, if you are going to go down that road, it would be better to have a separate database per client rather than a separate table per client. Another option would be a separate schema per client. – Thomas Apr 22 '11 at 16:37
  • In my case, two table per client its not typical situation. All client sprecific data will be stored in one table. – Alexandr Apr 25 '11 at 09:12
  • @Alexandr - If the schema is that small, then I would think that separating each clients data by a single column (e.g. ClientId) would be the most efficient. In addition, it is a more extensible solution in that it can easily accommodate an expansion in the schema. Databases like SQL Server are used to manage tables with millions of rows. It shouldn't be an issue. However, if you really wanted one table per client, then having one *schema* per client would be a better solution IMO. – Thomas Apr 25 '11 at 14:52