> DISCOVER MORE BLOGS

Selecting a Database: Flat File vs. Relational

An application engineer should be familiar with flat-file databases and relational databases

Determining the type of database to be deployed for a project is a combination of access requirements and preference. To make an informed decision on which to deploy, the application engineer should be familiar with the types of databases as well as the pros/cons of each. In this entry, we will consider two general types of databases and explore some of their applications and key points in the decision cycle when faced with a project.

“Flat File” databases consist of formats including single or multiple record types, and come in flavors of fixed-length definitions and delimited. The simplest form of flat file is a standard text file and consists of a single record definition. The record or “row” (as commonly referred to) repeats from one to many times, with each successive row representing a common definition. Every row is made up of a horizontal list of fields and the same definition of the row can be applied to every row in the file.

More complex forms of flat files would consist of multiple record definitions, where one or more records would have similar columns to others, yet some rows would vary in layout and content. These multiple record formatted files will use a template or record identifier to distinguish how the remaining items in the row should be interpreted.

Fixed length definitions structure each column across the row with a defined start and stop position. Delimited structures may have a predefined minimum and maximum length but the start and stop position of each column is indicated by a “special” character value.  These characters imply the end of a column and therefore can only exist within the data content of a column under special circumstances such as within quoted values (values surrounded by typically double quotes “).

“Relational” databases take on the challenge of storing data of differing definitions or formats separately in what is referred to most often as “tables.”  Each table will consist of two groups of columns identified as key values and stored values. The key values make it possible to relate records in one table to another in a parent-child relationship or dependency.

Flat file databases are typically independent of each other or self-contained and require no outside architecture to define or store the data for later interpretation. They can easily be edited and printed directly without interpretation beyond the file specification (layout).  Relational databases will, however, require a structuring “container” often referred to as a database “server” which stores and interprets the “metadata” defining the content.

Although there are sets of standards each relational database can be structured for, they typically require an “interpreter” tool to edit or view the data. These interpretation tools are quite often made available as part of the database server or as an additional related product and can be proprietary in nature. Some “generic” tools function using a specific standard and can be shared over varying database servers.

Some common flat file formats would include Electronic Data Interchange (EDI) platform standard formats such as X12 and EDIFACT, eXtensible Markup Language or XML, and CSV or comma-delimited files often representing spreadsheets. Common relational database servers would include Microsoft SQL Server, Oracle Database Server, and MySQL.

Flat file databases are most often used in a “transactional” nature and when entire file processing is required, where Relational Databases are generally found in data warehousing implementations where direct record access is essential.

The database ultimately adopted will largely depend on the nature and purpose of the data, the database access requirements, any cost/budget variations, company in-house technical expertise, and knowledge of the other applications needing to access that database. Flat-file databases are simple and are essentially “free” but limit data access to manual processes and/or structured programs. Relational databases are generally more complex with varying costs but provide advanced capabilities and more efficient access options.

CIC Platform Page
Best-Kept Secrets to B2B Integration Success GUIDE
How to Gain B2B Integration Control

One of the most efficient and effective ways to improve communication in 2023 is through B2B integration. And while there are plenty of B2B integration platforms and solutions on the market, a large majority of them fall short in one key area — giving you control.

WHITE PAPER WHITE PAPER
Your B2B Integration Strategy is Broken

If you can't support end-to-end API & EDI integration or if onboarding partners and customers is painful.... your B2B integration is broken.  Here's what to do about it.

VIDEO VIDEO
Demo Video Library

Instantly access our library of demo videos to see how Cleo Integration Cloud handles all EDI, non-EDI and API integration use cases.

CASE STUDY CASE STUDY
Mohawk Global Logistics

Mohawk Global Logistics cut EDI costs by $60K a year with Cleo. Here's their success story.

RECENT POSTS