Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

General Discussion

Neil Anuskiewicz
Neil Anuskiewicz
11,007 Points
Posted by Neil Anuskiewicz
Neil Anuskiewicz
11,007 Points

When to use a database and when to use flat files?

I was wondering what people though the criteria was for when flat files would be the way to go versus when a database would be the way to go? There are some obvious cases as when app is essentially an interface for a database but there are other sort of borderline cases where it's not clear. I'd love to hear people's thoughts on this. Thank you!

2 Answers

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

Use a flat file for a small amount of data that others don't need access to, at least at the same time you do. Even with Google Docs, etc., it's difficult for multiple folk to use spreadsheets.

Relational database management systems (RDBMSs) provide a number of significant advantages over flat file systems (e.g., Excel). There are many, many web sites that discuss the differences, and the advantages of one and the disadvantages of the other. You can google "RDBMSs vs Flat Files" to see a long list of such sites. You'll probably also find that there are some strong opinions in evidence.

Here are some of the high points.

RDBMSs give you:

  • the ability to establish users, roles and permissions

  • the ability to handle multiple users (if set up correctly), and, in the high-end systems, many simultaneous users

  • the ability to have a much larger number of rows in a table

  • the ability to join tables and model 1-1, 1-many and many-many relationships

  • the ability to have indexes, triggers, constraints, stored procedures

  • the ability to have data integrity checks

  • the ability to query the data efficiently

Flat file systems don't provide any of these, or if they do they usually do so badly.

Flat file systems are plagued with many problems (some call them anomalies), including the following (among others):

  • inconsistent data

  • redundant data

  • insertion anomaly

  • deletion anomaly

  • query difficulties

The problem is that many people know how to use a spreadsheet. Few have taken the time to learn how to use a RDBMS. Setting up a flat file is easy. Setting up a database in an RDBMS can be a bit of a chore.

But if you are doing a web site based on data, don't even consider a flat file.

Neil Anuskiewicz
Neil Anuskiewicz
11,007 Points
Neil Anuskiewicz
Neil Anuskiewicz
11,007 Points

Thanks for your thoughtful response. The things you say make a lot of sense. I recently took the introduction to Angular course where we built a todo list without a database and I started the MEAN course that involves building a todo list with a database. I'm sure what you're saying is exactly right that if it's an informal app (e.g., a personal todo list) then no need for a database but beyond that you quickly get into db territory.

By the way, do you have a preference for relational databases over Not Only SQL (NoSQL) databases (e.g., Mongo)? I know it probably "depends" but I'd be interested in your thoughts on this topic.

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

Well, as always it depends.

Re RDBMSs: If you are creating a web site for an airline with 300 flights a day, and allow reservations a year in advance, etc., etc., then you will need Microsoft SQL Server or Oracle. If it's a more "normal" website then MySQL is free on almost all hosting sites, there's a large community ready and willing to give support, you can download it free and run it under MAMP, WAMP or XAMPP. And it is used by a lot of web sites.

Although Microsoft Access or OpenOffice DB are great for learning, and have interfaces that make queries "drag-and-drop", you don't want to consider using either of them for any web sites.

Re NoSQL. I can't say. I've done a lot of web sites, all with databases behind them (SQL Server or MySQL), but none with NoSQL, so I'm not a good person to ask. I'm looking into MongoDB, but don't have enough experience yet to say. Since it's being used by some big companies, however, I assume it has a lot going for it. But the type of data is the key.

At the beginning of the MongoDB course the instructor says, if I remember correctly, that MongoDB was being used by those companies because their data didn't really fit the "old" model. So if yours doesn't, then MongoDB. But if it does, I'd recommend MySQL.