CODEWITHSUNDEEP
databasedesign-patternsdatabase-design
Code Grasshopper
Code Grasshopper

Reputation: 620

"Multiple company" design pattern

Is there such thing as a "Multi Company" design pattern for databases? We were being told the other day by a professor that this is a relatively simple feature to add in design time and that we should apply it to any software that may be used by more than one company at the time or for example by a corporation (D) that is made out of company A,B,C.

What he suggested was in general terms was this.

For example...

enter image description here

Is this an accepted way to model databases that will hold multiple companies'registers without mixing them?

Is there a better more efficient way?

I ask because it wouldn't be the first time we're told something that is quite outdated and I would appreciate any insight into current design trends (or where to find them)

Cheers.

Upvotes: 0

Views: 919

Answers (1)

mal-wan
mal-wan

Reputation: 4476

There are a few different considerations here:

Technical Considerations

From a purely technical point of view there's no reason that a Multi Company database should be modelled any differently than a Multi Anything database. By that, any categorisation will lead to that particular Category Id being propagated throughout the database as a Foreign Key to maintain category separation.

So from a Database technology perspective this is very simple and very possible.

Architectural Considerations

The type of application your database is supporting will also weigh into an appropriate design. For instance, if you were planning to host a Software As A Service application which was transaction heavy you may wish to run multiple instances for multiple companies to cater for thing such as performance, utilisation, licensing etc. This is one of a million examples of an architectural consideration outside the limitation of the Database technology.

So from an Architectural perspective you have many options including all companies in a single instance, multiple instances per company, or a mixture of the two (transaction heavy tables on a per-company basis and shared tables in a shared zone / database).

Legal / License Considerations

There may be issues for housing cross company data within the same database, or potentially even on the same machine (virtual or otherwise). This could also be a reason that requires you to rethink your architecture, which will in turn require a rethink in Database design.

Summary

As you can see there are many (and many more than I listed) reasons that could lead to an architectural change that then leads your database design in one way or another. But speaking purely technically, in a generic sense, there's nothing wrong with having a "Company ID" propagated throughout relevant tables and have your application or database level security operate to ensure that each company only gets their own data surfaced to them.

In real cases you'll have a lot more considerations that would influence your decision (I know many companies I've worked for have required separation of particular sets of data by law or regulation for instance).

Upvotes: 1

Related Questions