Column Mappings

Data Abstract introduced the concept of Column Mapping as one of the core features enabling cross database support within a single schema.

This cross database support means that a single schema can define the structure of the application's database to the middle- (and eventually the client-) tier, while allowing that same schema to target multiple different database backends at the same time.

To the application code all that matters is the structure of data tables as defined in the schema, it does not need to be concerned about the layout of the physical database.

Why Column Mappings?

In an ideal scenario, the layouts of all database supported by your application would be identical, and column mapping would not be necessary. In reality however, this is often not the case.

Reasons for targeting different database structures are manifold, including:

  • Differences in SQL dialects that enforce different naming schemes on different database backends
  • Targeting two or more existing databases that have been designed independently
  • Designing a new database for the application, while still needing to access an old legacy database during migration

In these cases you will typically have databases that contain similar data, but use slightly different table structures or field names to represent the same type of data. One database might have a Customers table with CustomerID, Name, etc., while the other database has a table CUSTS, with CUSTOMER_NO and CUSTOMER_NAME fields.

Mapping Differences in Table Layouts

The Data Abstract approach to abstracting such different database layouts is to define your data tables to represent the data in the way that you want your application to see it. This representation can (and usually will) be based on one of the existing database layouts, but could also be designed independently.

For each backend database, you will then create individual SQL statements that select the appropriate data, and define a column mapping that connects the fields from your database to the fields defined in the schema.

For example, CUSTOMER_NO would map to CustomerID, and CUSTOMER_NAME to Name:

Column Mapping

In this column mapping, Data Table Field is the field as defined in your schema - as mentioned before, this can match the field name in one of your database layouts, but can also be a fresh name (for example if all your database layouts used "awkward" or all-upper-case names, but you want proper PascalCase names in your schema).

Table Field is the name of the field in your physical database table, while SQL Origin is the name of the field returned from your query (which will be identical to the Table Field in most cases, unless your SQL query uses the AS directive to change field names).

Example

Lets see how Column Mapping can be used for targeting data table on the different database back-ends. In this simple example, the schema defines one data table (ListOfCustomers) with two fields: Id and Name.

Column Mapping List of Customers

This data table can be filled with data from two different database back-ends, Microsoft SQL Server or Firebird. A connection was crated In the schema for both database back-ends:

Column Mapping Connections

For the Microsoft SQL Server database, data is extracted from the Customers table within the Northwind database. For Firebird, data is extracted from the Employee database's Customer table. Column mapping was defined for each connection:

Column Mapping MSSQL mapping

Column Mapping Firebird Mapping

Data from the database backend can be extracted using an arbitrary SQL query or a stored procedure. This flexibility together with column mapping provides seamless use of existing data tables with any database sources.