Welcome to the Treehouse Community
Looking to learn something new?
Neil Anuskiewicz11,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!
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):
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.
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.