|Flat text DB vs Sql Db|
I need some advice...
Hi there people,
I have an issue that I would like an opinion/advice on. I have just decided to try using a text/flat file db over a mysql database, and would like to know what sort of advantages there are from one to the other, and visa-versa.
I have used only Sql Db's since I started coding years ago, but oddly enough, have never tried using flat file, so I am having a go at once today to see what benefits/pitfalls there are and could be.
Always good to have a learning curve once in a while ;)
Again, any advice would be greatly appreciated.
IF the db is mostly read only; just updated in a batch once or twice a day sort of thing then flat file is much easier to manage and uses less resources.
If there are constant updates a proper table database would be the best option.
The only pitfall of flat file is that it's not easy for locking type functions and this is only suited to read-only.
So your choice would depend on the # users, file size, fields and if the database is constantly updated or not.
|see what benefits/pitfalls there are and could be. |
As one who moved from flat file DB's to many DB engines over the years, I'm pretty vocal about this to the point of "goin' medieval." :-)
There are modules and extensions for just about any programming language for accessing flat file databases that do all this for you, but it still has to be done via programming, as opposed to it being inherent in the database engines. The more you allow the database engine to work for you, the less you have to program.
Lack of automation. Nearly everything a database does for you, you have to code yourself. Some really good examples:
- Field names. You need to maintain an array or have the first line of the database represent the field names so you can assign real values to columns (id, first name last name . . .
- Delimiting is automatic with databases, with flat files, you need to code it, which is not that simple at times. Are the fields quote qualified, if so, how do you deal with quotes in text? In a flat file, the newline is the "end of record" marker, how do you deal with newlines in fields?
- Sorting. Write a function to sort records by a field value, or do "order by fieldname desc." Which is going to make less work for you, and cost your client less?
- Data types. There are no preset datatypes in flat files, which means you have to program much more thoroughly and validate data manually.
Database operations. This, I think is the most burdensome of all.
- Searching. To match on a field, you must
-- read in the DB, line by line
-- split the line on the delimiters, store in array
-- locate the array index of the field
-- perform a strict equality match on the value, or a regular expression match
-- Not found? Move to the next line.
As opposed to
select * from table where field like '%$value%';
Which uses indexes to seek the matching records without reading in each and every record.
- Inserting/deleting/adding. Like the above, you need to read in the entire database line-by-line, store it in a second file or in memory until the matching record is found, REPLACE that line with new values (or skip it in delete,) then continue on to the end of the database, then overwrite the original with the new version. And pray nothing goes awry, even with file locking in place. Along the same lines . . .
No auto . . . ANYTHING. You have to create your own methods of generating unique record id's, there is no auto_increment. And in most cases this requires what? Yet another file to store the "current record number" in (or open the database, sort and get the highest number, increment it. Either way . . . )
Relational tables are a major pain to work with, but out of efficiency, you still have to try. There is no join. You have to write it, and figure out how to recreate the marvel of joins.
Functionality deficiencies. A brief look through some of the functions in the mySQL documentation, among which are the date and time formatting functions, will show you that you have to write programming that does all this wonderful stuff. What that adds up to is your flat file database application will have severe limitations and be extremely hard to maintain. Sometimes the slightest mods can require hours of work that would take a matter of minutes if your programming interfaces with a database.
In today's age there is no *good* reason to use flat file databases any more than good reasons for unwanted pregnancies, with the possible exception of old systems in place that were built on them (this site, for example.) Only the most severely disabled hosting services don't offer low cost database access and there is really no need to build new systems on flat file databases.
Unless you're experimenting, or don't expect growth and the needs will always be small. But on the Internet, that's seldom the case, we always hope to grow. :-)
One advantage of more modern frameworks / packages though is that can take flat files and do all the front end stuff for you.
Agreed in the older days it was not easy but now there are a good few apps out there which can work well with flat files.
I use both. Static info and flat file has some benefit. Dynamic info leans toward database. Pick and chose your battles. Then again, standardizing on one or the other will level the work and reduce distractions.
Thank you for all of your input, as rocknbil has correctly assumed, this is purely a selfish thing to see if I can do it, as I have only ever used mysql backends with anything I have ever done. My aim was to try to do a flat file option on my latest project - user management. Not entirely sure after reading these responses though whether I should carry on with it, but then again, the learning curve would be good, and if there is only going to be < 100 entries it may not be that much of an issue.
I would think too - that so long as the .txt file was stored outside of the root of the website, then malicious coding injection attempts would be a lot less probable, again, not being 'brought-up' on flat files, and depending on how the sanitizing was applied gauges how much risk there would be posed to the file - not sure - jury is still out on that.
I think, for the time being I will learn the mechanics of it, but stay primarily with Sql - have done for the last 8 years or so anyway :)
Again, thanks all for your opinions.