Understanding database structure is essential for the following tasks:

• To create custom reports, you have to know which data from which tables you would like to process and display;
• To create custom applications accessing TBM database (for example, to export or import data);
• To manually view raw database data and probably fix some issues.

TBM database structure is not very complex, as you may understand it by simply navigating through the application UI. When you open some table (for example, list of clients or vendor, or list of projects or jobs, or list of reports), you're viewing data from some database table. In most cases, internal table is name corresponds to what you see in UI, but with some exceptions. For example, list of projects is stored in the TranslationProject table. However, since we changed some terms with time, some internal names may differ from terms you see in UI. For example, client list is stored in the Customer table, services - WorkingType table, specializations - WorkingField table, etc. But in general it's very easy to find corresponding table.

There are few main concepts in the database that it's important to understand.

1. Each record in any table has ID column, which is key column uniquely identifying that record. If a record is referenced from some other table, it will be referenced by this ID. In TBM, we use GUIDs for keys.
2. Each field in any table has some data type. It can be string, date, number, boolean, or foreign key. Foreign key means that a field will hold ID of some record from referenced table.
3. When you open some data entry form in the application, you can see an entity may contain some nested tables. For example, customers have customer contacts, milestones, price-lists tables; vendors have languages, specializations, assignments; translation jobs have job tasks, job receivables, workload planner, etc. This means that on the database level there is a separate table for such nested table. If to take jobs and job tasks, then jobs are stored in the TranslationJob table, while tasks are stored in the TranslationJobDetail table, which contains TranslationJob column holding a reference (foreign key) to a master translation job.

To view data in the database, you may use the following official tools:

• Microsoft SQL Server Management Studio - for SQL Server databases;
• pgAdmin - for PostgreSQL databases.

Additionally, we recommend to use DbSchema tool, which provides visually attractive way to view structures, view data and manipulate it, without relying too much on SQL queries. See video above for details about this tool. Here, you can find a file generated with this tool, which visualizes TBM database structure, making it very easy to examine tables, foreign keys and data types in database fields.

You can download this file from here:

TBM Schema Example.zip

And here is a partial screenshot from this tool demonstrating Vendor tables structure:

You can quickly navigate through tables, analyze columns and their data types, view foreign keys by hovering mouse pointer over field name, which will highlight a "connected" table, so you can quickly see from where a data is coming to that field. In the attached zip file, you will find HTML document which contains this schema, and list of all tables and their structures below in the document.

Knowing this information, you can build custom reports much easier. For example, in the video above we show how to build a report which will list all your vendors along with their supported languages and provided services.

Also, it is important to note that when building reports, you get access not only to fields and tables existing in the database structure, but to some additional fields and tables, which do not physically exist in the database, but are created by application, in memory. For example, for translation jobs two table exist in the database: job tasks and job receivables. However, when printing information about a job we should use one of these tables: receivables if any info exists in this table, or tasks, if receivables are empty. To help solve this task, translation job has non-persistent (not stored in database) table named Rows to print. This table is populated by application code. Another good example is invoice: there is invoice items table, but in memory you also get access to Rows to print table, which contains extended data from the Invoice items table - invoice item is linked with some translation job, and that job may contain multiple tasks. Rows to print solves this - it splits each invoice item according to a number of tasks from a linked job. Better to present this visually. Imagine that invoice has one linked job:

We can't fill columns 2-5, because that job has 3 services. In this case, Rows to print will have such contents:

So, it helps to print invoice data in custom reports.

There are a lot of such non-persistent fields, which you can find while working in report designers. However, their names are always self-explanatory, so you can always suggest their meaning.

Since database structure is not an easy topic, please ask any questions on our forums.