Flat Files vs. Relational Databases
At KELL we work with data a lot. I mean, a ton. Along the way we've learned some important lessons, tips and tricks. However, for many people we need to start with the basics of data management. Let's first define the difference between a relational database and a flat file. Here's a nice 2 minute video that illustrates the difference: https://www.youtube.com/watch?v=B-l3VQuxYrs (this is not a KELL video).
Think of a spreadsheet as a flat file. It may contain dozens of columns, but it is "flat" because most often the spreadsheet is not linked to other spreadsheets. Most database products allow users to export their data in a series of flat files, e.g. here's a .csv of your Contacts, and here's another of your Donations. If you're looking to migrate from one database to another, sometimes this is the only way to get your data out. It's very unfortunate because you'll likely lose some valuable information that's buried in the data, but this may be your only choice.
Examples of flat files: Excel (XLS or XLSX), Comma Separated Values (CSV), Tab or TXT Separated (.TXT).
Relational Database Management Systems (RDBMS)
By definition, a database is a series of related entities (often called Tables or Objects) linked together using ID values. For example, donor John Smith has an ID of 1234 and his record is stored in the Contacts table. When we look at the table storing all donations we see several linked to ID 1234, but we won't find any information about John Smith in the donations table. That's because all of John's information is in the Contacts table, linked by his ID.
For nonprofits, there are often numerous related entities tied to a Donor, to a Donation and so on. For example, perhaps the gift is being allocated to a Fund, and there are numerous tasks associated with the gift, and the donor's spouse receives a soft-credit for the gift. As you can see, these related entities quickly add up.
Examples of relational database files:
- SQL (Structured Query Language) is the most universal and widely used relational database. If you have access to the SQL Server, you should be able to create a .bak (backup) of your data. Here's a tutorial which may help: https://www.youtube.com/watch?v=VvaKmujEE7E
- Microsoft Access has evolved it's file types, so depending on which version you're using, you might see: .accda, .accdr, .accdt or .mdb
- FileMaker Pro creates a .FMP file.
- MySQL is an alternative to SQL. The .frm file defines the structure and the .myd is where the data is stored.
Many popular nonprofit database products utilize these relational structures. When you are looking to migrate from one of these products, you'll want to provide KELL with a backup of the relational database if possible.