Text File Based Vs SQL Databases

[Kuta]

I'm hoping there's some experienced programmers in here. What I'm asking is if I was to create a website and used PHP to develop a text file based database then would there be any limitations or disadvantages compared to creating a SQL database? I already know that PHP locks files out while a script is accessing it. There should be some work arounds for this but for the time being I need to determine if I could have a website which could expand indefinately by using only text files and images. The files would be arranged by file name and a directory structure so I'm also wondering if scripts had to navigate down into a deep directory tree would that slow things down much? Has anyone actually experimented with speeds of accessing files in directories?

 

[Runik]

From experience, only use text file for really small websites, with a database that doesn't change much ... if your site must expand, text files aren't worth the hassle.

And if something bad happens to your file, you can kiss your data bye bye

MySQL isn't that difficult to handle, and seems to be more appropriate for what you intend to do

I tried text file database for my worklog, and I ended up using MySQL in the end

 

[ExCyber]

It really depends on what you want to do. It's possible to build a halfway decent kind of pseudo-database by using a carefully structured directory tree, but if you want to do anything more than grab specific files (whose paths are known or can be constructed in advance from e.g. ID numbers or resource names) and totally replace files with new versions, you'll likely find yourself running into performance problems down the road. Database systems aren't the solution for everything, but they do exist for a reason.

 

[Kuta]

I can see you guys are both pro-SQL but I'm not sure you are visualising the same thing I am. The reason I thought SQL databases were so popular is because they are designed to make life easier. This would come at the cost of maintenance, reliability and speed. MySQL would just become another piece of sofware I'd have to keep up to date and learn how to use. It's just another thing that can break and bring your site down. PHP should do as it's told. Once you've written it and tested it then it should keep working forever.

The big advantage of creating a text based database I thought was the speed. Reading and writing data from text files should be lightning fast. The big drawback being the amount of code you would have to write. Do either of you have much experience with PHP? Do you know if it slows down when reading and writing files? There's no way I can imagine SQL ever being faster than text files.

 

[ExCyber]

You seem to be reading a lot into the term "database" that isn't there. Database access is not inherently faster or slower than file access; they are efficient for different kinds of operations. File systems are good at storing and retrieving variable-length streams of bytes, which might need to grow or shrink. Databases are good at storing and retrieving structured records, usually of fixed size. File systems and databases are often used in conjunction, with a database acting as an index of structured metadata for data stored in files (this is basically what a file system is in the first place, except that it only stores essential system information about files).

Again, it depends on what you're storing and what you want to do with it. Files let you read, overwrite, or append to a chunk of data quickly assuming you already know where it is. What's expensive is if you need to re-read the entire file every time you want to retrieve a record, or re-write most of the file every time you want to insert a record (mainstream operating systems have no "insert in middle of file" operation).

If performance and administration are major concerns, you may want to look into a lightweight in-process database such as Berkeley DB, cdb, gdbm, or dbm.

 

[Kuta]

Some interesting DB software there. I'll probably need to familiarise myself with all the DB software offerings out there before making a solid decision. I'm new to this kind of stuff so I think I'll start off designing my system with text files and see how far I get. I don't mind learning things the hard way.