[depolitic]

Question: How does a flat file database system compare to an SQL database.

The reason I ask is that allot of systems CMS, Blogs, and Wikis depend on flat files to store their data mostly in xml formatted files.

Now what is the advantage to such an approach when you have access to sql - other then that you do not needing a database means you are more portable. Since it seems that sql databases are not truly portable unless you starting using abstraction layers (Added complexity means added pain in my view)

However then sql gives you a common interface (Mostly) to interact with your sql database.

Also since a Database is essentially a binary file that is highly optimised for searching versus a text file. Is an SQL database anymore free from being corrupted since I have seen text files become corrupted when the server crashes - (Windows Haa) -.Can corruption still happen with an SQL database via file system corruption, and is file corruption anything to majority worry about with a text file database.

Any thoughts.

[larkost]

The simple answer: flat file are really fast when you are going to read everything into memory anyways (and presumably are going to do something with every piece), or you are going to be working with really large contiguous pieces of that data.

Databases are good at filtering out a set of related lines of information and linking them up with other pieces of information. They are slower than reading flat files, but much faster at finding a small piece of information than going over the entire file.

So, if you have a lot of data and are only displaying a small sub-set, then there is a good chance that a database will provide a benefit. However, if you are dealing with a small amount of information (like most CMSs are) then adding a database is probably just more overhead. Where to draw that line is a tricky thing because there is a lot more complexity to the decision: databases take most of the load off you for persistence (saving the data), but they require that you set them up and run by their rules.

On the corruption side... well either sort of file can become corrupted, and both system s work to prevent this.

[hayesk]

There's no such thing as an SQL database, per se. SQL is just a language for querying a database (Structured Query Language). Flat text files could easily be the storage mechanism for an SQL database. But I would expect such a system to have binary index files, for efficiency reasons - but even those could be in text format as well, although I see no reason for it, since they could easily be regenerated and would almost never be edited by a human.

Getting back to your questions, Wikis and Blogs, etc. use flat files because they are human-editable, and the reader typically loads the whole thing up (or a range) anyway, eliminating the need for complex indexing. If the site was cross referencing (i.e. if you could make a post that appeared on two blogs, or in two forums, etc.) then an indexable database would be better because the data might not necessarily be stored sequentially in a single file.