Join Sheeri Cabral for an in-depth discussion in this video Relational databases vs. flat files, part of Up and Running with MySQL Development.
- View Offline
When talking about relational databases versus spreadsheets, I explained a bit about what relational databases are. You may be thinking, I just put everything into a regular TXT file. If you are a power user, maybe you'll use GNU tools such awk, sed and grep to find, manage and change the data in your TXT file. There are definitely some similarities between flat files and relational databases. Both can be manipulated to show information in a new form without changing the original data.
Both allow you to extract data out of certain fields. And both have powerful matching and filtering capabilities. Almost all text editors have some kind of global search and replace functionality and relational databases can do that too. Depending on your text program, you might have formatting capabilities like font size, and highlighting. Maybe you have your mother's phone number in big, bold letters so her number is very visible for when you want to call her. Sorting is also pretty tricking in a text file.
A text file allows you to put anything into it. We can put a string, like Hello There, or we can put a number like 172, and our program won't complain, even if it's supposed to be the field for a date. If we want to be able to use tools to manipulate the data, we might have some rules. If we are using a tab-delimited file, the rule is that the pieces of data are separated by tabs and that they appear in a certain order. There's also nothing stopping us from using a tab to separate the data on one line, but using a comma to separate the data on the next.
The program does not complain, but when we try to use tools to get the data we want, we won't be able to get our proper data. A database will separate the fields for us, a text file does not. We have to enforce a structure if we want our text file to work in a certain way. Like a spreadsheet, text editors have display information stored with the data itself. Text editors are very good for writing text, laying out text on a page, and moving text around. You can cut and paste and easily move a word, paragraph or even several pages of text in a text editor.
They are designed for storing, viewing, and changing documents that consist mostly of text. A text file is sometimes called a flat file especially when comparing to a relational database. This is because a relational database is built to highlight how some data relates to other data. Flat files are great for writing and editing text. They are also good for moving lots of text around. Databases are good for storing information that you want to be a certain type. Databases separate the data from how it is displayed which can eliminate duplicate information and reduce data errors.
Text files are handy, but a database is a much more powerful tool when manipulating data.
- Exploring MySQL data types
- Creating a database
- Creating tables
- Reading and adding data
- Using date and number functions
- Sorting results
- Inserting and replacing records
- Joining tables