0

I am making a client management application in which I am storing the data of employee , admin and company. In the future the database will have hundreds of companies registered. I am thinking to go for the best approach to database design.

I can think of 2 approaches:

  1. Making all tables of app separately for each company
  2. Storing all data in app database

Can you suggest the best way to do that?

Please note that all 3 tables are linked on the basis of ids and there will be hundreds of companies and each company will have many admin and each admin will have hundreds of employee . What would be the best approach to do with security and query performance

Improve this question

5 Answers 5

Reset to default
1

With the partial information you provided, it look like 3 normalized tables is what you need, plus the auxiliar data like lookups and other stuff.

But when you design a database you would need to consider many more point like, security, visibility, client access methods, etc

For example if you want to ensure isolation, and don't allow users to have any visibility to other's data, you could create dynamically a schema per company, create user and access rights for each schema dynamically. Then you'll need support these stuff in the DAL, which in fact will be quite fat.

Another approach for the DAl could be exposing views that always return subsets for one company.

A big reason reason that I would suggest going for the normalized approach is that maintenance will be much easier this way.

From a SQL point of view I don't see any performance advantage having many tables or just 3, efficiency of the indexes, and smart DAL will make the difference.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

thanks for the reply . So scenario is that i will be having hundreds of companies each company would have many admin and each admin have hundreds of employee . Now what you say should i store all values in 3 tables or i make replica of 3 tables for each company . If i make replica than data will be in more organize way . what you say
@user1224233 on the contrary, I'm not saying that having data in many similar tables will make things more organized, it would only have some benefit on the security point of view, but it will take much more effort to create and maintain. In this specific case, few normalized tables will be much more organized.
I totally agree with you reason i think for separate table is the security thing only. in my scenario each company has no link with another company and in second approach putting all data in 3 tables only i think about security issue and query response time as this could be possible that only one company has so much data and only because of that query delay occur
The security can be handled by giving visibility thru views only. The database schema doesn't need to be the same that you show to the client. Since you are not providing any information about environment that you intent to work with, I'm very limited on advice and I'm sticking to generalisms. As for performance I don't think that queries to databases will cause much impact to pieces of data in different companies, modern DBMS already manage them exceptionally well these days, and if you are expecting huge amount, you should already design for big data and NOSql
thank you so much for reply , your messages are really helpful environment will be web + mobile app . So i think i should have clustered indexing for all three tables and store data of all companies in 3 tables only and to retrieve security i will user View
|
1

The performance of the query doesn't much depends on the size of table but it depends more on the indexes you have on that table. so you need to put clustered and non clustered indexes as per your requirement and i can guarantee that up to 10 GB of data you will not face any problem

Improve this answer

1 Comment

i read about clustered indexes and it says it will store the value near to the other table so i have all three tables interlink so i would go for clustered indexing for all three tables . Please correct me if i am wrong . Thanks
1

This is a classic problem shared my most web business services: for discussions of the factors involved, Google "multi-tenant architecture."

You almost certainly want to put all companies into a common set of tables: each data table should reference the company key, and all queries should join on that key, among their other criteria. This allows the best overall performance, and saves you the potential maintenance nightmare of duplicating views, stored procedures and so on hundreds of times, or of having to apply the same structural changes to hundreds of tables should you wish to add a field or a table.

To help assure that you don't inadvertently intermingle data from different customers, it might be useful to do all data access through a validated set of stored procedures (all of which take the company ID as a parameter).

Hundreds of parallel databases will not scale very well: the DB server will constantly be pushing tables and indexes out of memory to accommodate the next query, resulting in disk thrashing and poor performance, as well. There is only pain down that path.

Improve this answer

Comments

0

depending on the use-cases of your application there is no "best" way. Please explain the operations your application will provide so we can get further insight into your problem.

The data to be stored seemed to be structured so a relational database at a first glance would work out well, but stick to the point i marked above.

Improve this answer

Comments

0

You have not said how this data links at all or if there are even any links between them. However, at a guess, you need 3 tables.

  1. EmployeeTable
  2. AdminTable
  3. CompanyTable

Each with the required properties in there, without additional information I'm not able to provide any more guidance.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.