When setting up a process to translate data from your backend database to another format, it is common to realize that your source DB data is too disparate with how your target data format needs to be mapped/structured. Introducing database views can often simplify integration between two formats and reduce or eliminate the need for external programming.
A view is simply a named select statement that is stored in a database as an object. Using a view can simplify the mapping process by renaming fields (rows) to a purpose more easily understood. For example, let’s say there is an address table with the following field names for “City” (SZMC1), “State” (SZMC2) and “Zip Code” (SZMC3). The data in these fields needs to be translated to the “N3” segment in an EDI document. When adding these fields to your view, you can rename them to something more logical like: CTY, STATE, ZIP. A data map created from this particular view would also be easy to maintain for other users who are only familiar with the target data structure. (In this case, a user who is familiar with EDI but not the source DB data)
Views can also be used to link tables across database schemas and even tables from another database using a remote connection. A single view could be defined with an “SQL Join”, which is a compilation of related columns or rows in multiple tables. It is common to have multiple tables containing information necessary for one target record (a “many-to-one” relationship). A user could potentially face having to deal with using complex logic in their data map to bring all the related source data together from separate tables for a corresponding target record. In this instance, a view could prevent this kind of mapping complexity by providing one location for the source data (a “one-to-one” relationship). For everyday end-users, the view also hides the fact that this information actually originates from numerous tables. The creation of views using complex queries, such as joins, allows simplified commands for an end-user unfamiliar with joins or cryptic business rules resulting from disparate data.
Views can also provide a simple but effective security mechanism for data translation processes. Through a view, end-users running these processes can access only the data they can see. A user with administrative privileges can configure it so that other tables in the database and view are neither visible nor accessible by an end-user running a translation process. For example, a view could be designed so that a record will only be visible if a field (row) in one of the tables is updated with a particular value. Only the administrator would have access to that specific table, controlling what data is ready for translation by which user the process is running under. The use of a view can also shield users from changes made to the actual tables.
Lastly, setting up a view properly can significantly decrease the system resources required during data selection. Using a real-case example, a “database to XML” translation process that took eight hours to complete was reduced to three minutes with the use of a “view”. The customer’s database was analyzed and it was concluded that creating a view with specific indexing would greatly improve the efficiency of the data selection. Creating a “view” can help a translation process run much faster and see that this data gets to the customer much sooner and more efficiently.