Within each database you have as many companies as you want to create. Each company set of data is separate so you may have two or more trading companies and then a group consolidation company.
Alternatively it’s usual to sometimes have a ‘training’ company, which is a copy of your trading company at a point in time, for you to test and train on without risking the integrity of you live accounts. There is a function within Microsoft Dynamics NAV now to create a new company and copy all of the data from an existing company into it. Beware of using this in the working day though as if you have a decent amount of data it can take a while and will have a performance impact.
The data for each of these companies is stored in its own set of tables in that same database and in fact each table name at the SQL level – start with the company name so for instance CRONUS_UK_LTD$Customer would be the name of the customer table for the Cronus UK Company. While having the tables split like this makes reporting across companies much harder, it does mean that if you have multiple companies the users in one set don’t impact the perforce of users in another.
Each Dynamics NAV licence you have allows you to have one database with as many companies as you want, although in the past some very old modular licences made you buy per company unless you purchased unlimited companies granule. Thankfully Microsoft did away with that idiocy when they introduced business ready licences in I seem to remember, 2004.
Although you can have only one database, the licence agreement does allow you to have a second database for non-production testing purposes during, for instance, upgrading. It cannot be used for trading companies however.
Per Company or Per Database Tables
Having said in my previous post on Companies that all the tables in a company have their own independent tables, that not strictly true. There are as standard in every Dynamics NAV database a few that are per database, rather than per company.
The standard ones are the user and permission set tables as well as the company table that holds the list of companies in the database. If you think about it it’s logical that these cannot be per company, as they hold information about the different companies themselves so that a user can be restricted to a particular company for instance.
What you should understand is that whether a table is per database (and therefore holds the same data in every company in that database) or per company it is a property that can be changed on that table.
Changing some standard tables to be per database can make data management much simpler -especially for a group of companies where you want consistent values in every company. Examples of where this works for me are Dimensions and Dimension Values, Payment Terms, Payment Methods, Currencies and currency rates plus item categories and product groups.
There are many tables which it’s not safe to do this on however, and my rule is, that any table that has a flow field should NOT be changed to per database. The classic one we are asked for is the Item table so that you only have to setup the item once for every group company to be able to use it. Trouble is which companies item ledger entry table does the Inventory field look at if it is per database and which companies transactions (or all of them?) does it use, to calculate the cost?
It’s only useful for static parameter type data, and as that rarely changes, is it worth changing from standard? That’s especially true when those tables can be cut and pasted from one company to the next.
So useful to know that you can, important to know where you shouldn’t, legitimate to consider especially for custom tables with no flow fields.
Note
This post is part of a 6-part series. A link to all the posts in this series are below;
- Why Do You Need to Understand the Technical Structure of Dynamics NAV
- Part 1: Clients & Servers, Executables and Objects & Databases in Dynamics NAV
- Part 2: Object Types, Tables, Pages, Reports, XML Data, Code Units, Query Object Types and Menusets in Dynamics NAV
- Part 3: Object Numbers, Object Licensing in Dynamics NAV
- Part 4: Web Services – interface method of Choice in Dynamics NAV
- Part 5: Companies and Per Company or Per Database Tables in Dynamics NAV
- Part 6: Multi-tenancy – What Does it mean?
