Handling Database Relationships
Relational databases, as their name implies, normally have relationships defined between their tables. Taking as example the Altova.sqlite database found in the folder <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\, several relationships exist in it, for example:
•The sample company (corresponding to the table "Altova") consists of one or more offices (for example, in Brenton and in Vereno). In database terminology, there is a one-to-many relationship between the "Altova" and "Office" tables. In other words, for each PrimaryKey record in the "Altova" table there can be multiple ForeignKey records in the "Office" table. Any "Office" record where ForeignKey value corresponds to PrimaryKey value in "Altova" should therefore be considered an office of "Altova".
•Each office consists of one or several departments (for example, "Marketing", "IT", "Development"). Again, there is a one-to-may relationship between "Office" and "Department" tables.
•Finally, each department consists of one or several people. Hence, the one-to-many relationship between the "Department" and "Person" tables.
Relationships between database tables are important for mapping purposes. MapForce keeps track of such database relationships when you add a database to the mapping. This enables you to preserve the database relationships when mapping data to or from a database. To understand this concept better, add the Altova.sqlite database to the mapping (using the Insert | Database menu command). Let us call each of the tables below a "root" table:
"Root" tables
Expanding a "root" table displays all related tables beneath it in a tree view. For example, if you expand the Office table, notice how the related table hierarchy is displayed:
•A left arrow ( 
) in front of a table denotes that this is a child table. For example, Address is a child of Office. Department is also a child of Office, as well as a "sibling" table of Address, so both have the same indentation level. As you can see, the relationship on the mapping corresponds to the "Relationships" diagram above.
•A right arrow ( 
) in front of a table denotes a parent table. For example, Altova is a parent of Office.
This hierarchical representation of tables helps you preserve the existing database relationships when your mapping reads from or writes to a database. For example, let's assume you want to get all the records from the Person table into an XML file, grouped by their department. Specifically, your XML file should link every person to a department, similar to the Altova.sqlite database used in this example:
As illustrated above, the "Administration" department has three people, "Marketing" has two people, "Engineering" has six people, etc.
When mapping data from this database, if you want every person to be distributed to the correct department, it is important that you use Department as "root" table, and then map from the Person table which is child of Department:
The mapping above is a modified DB_Altova_Hierarchical.mfd from the <Documents>\Altova\MapForce2023\MapForceExamples\ folder. When you preview the mapping, the result is that each person is grouped by department, which was the intended behaviour. That is, "Administration" has three people, "Marketing" has two people, "Engineering" has six people, etc.
Now have a look at the slightly modified mapping below, where connections have been deliberately drawn so that both Department and Person are "root" tables.
This time, when you preview the mapping, all persons (regardless of their source department) are grouped under each target department, which was not the intended behaviour. That is, "Administration" has 21 people, "Marketing" has 21 people, "Engineering" has 21 people, etc.
In the second example, the database relationships are disregarded, due to the way the connections were made.
Therefore, when you want to preserve database relationships, make sure that connections are drawn to or from the same "root" table, which contains the child tables whose relationships you want to preserve. This works in the same way for both source and target databases. For examples of database mappings which preserve relationships, see the DB_Altova_Hierarchical.mfd and Altova_Hierarchical_DB.mfd files available in the <Documents>\Altova\MapForce2023\MapForceExamples\ folder (see also Inserting Data into Multiple Linked Tables).
There might also be cases when you do not want to preserve database relationships. For example, let's assume that you want to export all data from the Altova.sqlite database to a flat XML file adhering to the SQL/XML specification (Part 14 of the Structured Query Language (SQL) specification). This kind of mapping is illustrated by the DB_Altova_SQLXML.mfd sample, available in the <Documents>\Altova\MapForce2023\MapForceExamples\ folder. The goal of the mapping is to get database data as a flat XML file. The target SQL/XML schema was generated with XMLSpy, using the Convert | Create XML Schema from DB Structure menu command.
DB_Altova_SQLXML.mfd
As illustrated above, every database table has a corresponding element in the target XML. When you preview the mapping result, you can see that the actual database rows from each table are written to "row" elements in the target.
As the XML output shows, no hierarchies exist between the XML elements; it is a flat SQL/XML structure. The database relationships were ignored, because we intentionally mapped data from multiple "root" tables.