Database Design and Companies

Database Design and Companies

Microsoft Dynamics AX 2009

A Microsoft Dynamics AX database can contain information for one or more companies. Multiple companies can share the database while using the same application logic. Each company has a set of data that is only for that company and cannot be accessed by another company. Conversely, some tables might contain data that can be shared by multiple companies. The company design in Microsoft Dynamics AX includes the following concepts:

Companies – Based on one or more virtual companies. When adding data to a table that is not in a virtual company, the data is stored directly in the company.
Virtual Companies – Contains data from multiple companies that is shared by all the companies. The virtual company uses a list of one or more table collections to define which tables it contains. The end user can't access tables directly through a virtual company—they can only access the tables through a company. The virtual company is transparent to end users.
Table Collections – Specifies a list of table names. Table collections define a set of tables that have no foreign key relationships with tables outside the table collection. Each table in the system occurs only once in any one table collection, but tables can be added to more than one table collection. There is no table data stored in a table collection—only companies and virtual companies store data.

The following diagram shows how companies, virtual companies, and table collections interact.

Companies, virtual companies, & table collections

Courtesy of Microsoft Press, Redmond, Washington

Table collections and virtual companies are used to separate or share data

Company AAA and Company BBB share the Currency tables. Company CCC and Company DDD share the Chart of Accounts tables. All companies share the Countries and Zip codes tables. As the figure shows, a virtual company can be associated with multiple table collections. A company can also be associated with multiple virtual companies. For example, Company AAA has access to virtual companies Currency and Zip codes.

When to use Companies

Tables that contain shared, static information, such as Zip codes, are usually assigned to a virtual company that contains similar static information. This allows information to be accessed by users that work with several unrelated companies, such as an accountant. Although an accountant works with multiple companies, the information in the ZIPCode table can be accessed from all the companies in the database.

Centralizing data is more efficient because it's stored in only one location instead of in each company. This also improves data integrity because the table needs to be administered only once.

Where Data is Stored

Companies and virtual companies are used with only table data. When a new table is created in Microsoft Dynamics AX, that table is available to all companies. The data in tables is stored on a per company basis. The definitions of the tables, however, are stored separately and have global scope within the application.