1

In my application, users (and all data) is grouped by company. So, for example, every customer in the database has a company assigned to it, as does every product etc. The user can log in with their company ID and they can only access the information from that company, however this feels like the wrong way to do things (having all data in the same tables with only the company ID separating them).

My two problems are:

  1. Say someone from company 1 makes 2 products, with product ID 1 and 2, then someone from company 2 makes a product, it will have ID 3 unless I make a (what I believe is unnecessary) query to count the number of products and add 1.
  2. I just don't feel it's secure to have all the data this close together, and it's going to cause huge bloat of the database when/if we have 100+ companies all with their 100+ products and customers in there.

What is the best way to go about this, or is this the best practice and I'm just seeing ghosts?

Improve this question
1
  • 1
    A lot of systems use the same design as you're already using, and it's fine. Other systems follow the one-tenant-per-database pattern instead, where each customer would get their own database. There's not a standard way that's always better than the other, just depends on your goals. One reason I could be in favor of splitting the data out into separate Tables (either within the same database or their own databases) is to improve the statistics for the data distribution in the Tables when there's a high variance between customers, to help performance, but this is somewhat edge case scenario.
    – J.D.
    Commented Sep 22, 2022 at 12:32

2 Answers 2

Reset to default
1

Best depends on what the important criteria are, but your options include:

What you are currently doing.

Row level access control and a different database username for each company, (if your server platform suppoets that)

A diffreent set of data tables for each company (eg in a different database or schema)

A different database server (server process, virtual server, or physical hardware) for each company (if you have enough income from the company to support that)

Me picking one of the above as "best" would be mostly based on opinion and I don't ave enough information to even begin to form an opinion.

One financial service provider that I deal with includes "company name" on the login form - that may be so that they can choose the right database to find my login.

You seem to be dealing with money, and people get angry if you misplace their money. I would use distinct shemata for each company as a minimum. this will also help when a company wants a backup of their data, or wants their data deleted, as you don't need to painstakingly filter it out of shared tables.

Improve this answer
1

Looking to your scenarios, I would suggest rather to have different database schema company wise which have same copy of database object structure across all the company wise schema.

Each of definition change would make in dbo OR specific company wise schema, will trigger (database trigger) to all other company's schema and propagate the change.

So being on the same database, you can aggregate your product or any other data w/ or w/o companies data.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.