Database schema for multiple companies

Question

I am working on a inventory app using c# and entity framework code first approach.

One of the design requirements is that user should be able to create multiple companies and each company should have a full set of inventory master tables.

For example each company should have its own stock journal and list of items. There would also be a way to combine these companies in future to form like a 'group' company, essentially merging the data.

Using one of the file based RDBMS like sqlite, its very simple, I would just need to create a separate sqlite database for each company and then a master database to tie it all together. However how should I go about doing it in a single database file! not multiple file databases. I do not want to have a 'company' column on every table!

The idea that I had given my limited knowledge of DB's is to separate using different schemas. One schema for each company with the same set of tables in each schema, with a separate schema holing the common tables and tables to tie up the other schemas together. Is that a good approach? Because I am having a hard time finding a way to 'dynamically' create schemas using ef and code first.

Edit #1

To get an idea of the number of companies, one enterprise has about 4-5 companies, and each financial year the old companies are closed off and a fresh set of companies created. It is essentially good to maintain data for multiple years in the same file but it is not required as long as I can provide a separate module to load data for several years, from several of the db files to facilitate year on year analysis.

As far as size of individual companies data, it can hit the GB mark per company.

Schema changes quite frequently at least on the table level as it will be completely customizable by the user.

I guess one aspect that drives my question is the implementation of this design. If it is a app with discrete desktop interface and implementation and I have my on RDBMS server like SQL Server the number of databases do not matter that much. However for a web-based UI hosted on third party and using their database server, the number of databases available will be limited. The only solution to that would be to use serverless database like SQLite. But as far as general advice goes, SQLite is not advised for large enterprise class databases.

Answer 1

You've provided viable solutions, and even some design requirements, but it's difficult to advise "what's best" without knowing the base requirements like:

• How many companies now - and can be reasonably expected in the future
• How many tables per instance
• How many records per 'large' table, per company
• How likely are things to change frequently, dataschema-wise

With that in mind, off to some general opinion on your solutions. First off, considering the design requirements, it would make sense to consider using seperate databases per company. This would seperate your data and allow for example roles and security quite easily to be defined on a database level. Considering you explicitely mention you could "make it simple" using this approach, you could just create a database (file) per company. With your data access layer through Entity Framework you could also easily change connection strings between databases, and even merge data from A=>B using this. I see no particular reason, besides a possible risk in maintaining and updating different instances, why this shouldn't be a solution to consider.

On the other hand, using the one-big-database-for-all approach, isn't bad by definition either. The domain of maintenance becomes more compact and easily approachable. One way to seperate data is to use different database schemas, as you suggest yourself. However, database schemas are primarily intended to seperate the accessability on a role based level. For example, a backoffice employee e.g. user role should only communicate to the "financial" schema, whilst the dbo can talk to pretty much anything. You could extend this approach on a company base, seeing a company as a "user", but think of the amount of tables you would get if you have to create more and more companies. This would make your database huge. Therefor, in my opinion, not the best approach.

Finally, I'm intrigued by your statement "I do not want to have a 'company' column on every table". In my opinion, you should consider this as well. Having a discriminator property, like the companyId column on several tables are pretty easy to abstract using Entity Framework (or any ORM for that matter). This is what the concept of foreign keys is all about. Also, it would give you the advantage of indexing this column for performance. Your only consideration in this approach would be to make sure you provide this 'company discriminator' on all relevant tables.

The latter would be quite simple to enforce using EF Code First if you use a contract for each seperate data class to inherit from:

interface IMyTableName {
    int companyId;
}

Just my quick thoughts, though.