[RESOLVED] Flat file vs. MySQL

wyattstorch42

I'm here to ask for advice rather than ask for code critique. I read the rules and I am not posting code, so forgive me if I am asking in the wrong place; I just didn't know which forum would be the most appropriate.

Here's the scenario. The "client" wants a PHP script that serves as an image gallery. He will upload the images via FTP to a certain location and the script will generate thumbnails. The images will be sorted into subfolders ("albums") in the gallery folder with a structure as deep and flexible as he needs. He also wants an interface that can be used to append titles and descriptions to images and albums. Lastly, he wants a system to be used by his site guests to rate and comment on images and galleries.

Basically, in terms of resources, do you think it would be more logical to use a flat-file system to store the information or a database structure? Obviously, using a database is cleaner because all of the information is clearly stored in one place. But, correct me if I'm wrong, but querying a database is heavier on server resources than, say, reading an XML or CSV file.

Let's say that the client has 2000 images. If I store the data for his images in a CSV file like so:

"path/to/image.jpg", "Title 1", "Description 1"
"path/to/other/image.gif", "Title 2", "Description 2"

Will it take a lot of server effort to convert this CSV file into an array and then search that array for the image that I need to recall the title and description from?

And my bigger concern is a master file that contains comments, e.g.

"Commenter", "http://www.commenters-url.com/", "{timestamp}", "Comment goes here.", "path/to/image.jpg"

This file would likely have a lot LOT of comments.

Basically, through the million words I just used to ask this simple question, I am trying to get at this: is querying a database table with thousands of rows to locate, say, all of the comments associated with an image in the "image" field of the table as much of a load on server resources as running through a massive array in search of data? If I use MySQL, do I have to develop a caching system or something?

dagon

db,db,db with out question a db. there is 0 advantage to a flat file over a db in this situation

laserlight

wyattstorch42 wrote:
Basically, through the million words I just used to ask this simple question, I am trying to get at this: is querying a database table with thousands of rows to locate, say, all of the comments associated with an image in the "image" field of the table as much of a load on server resources as running through a massive array in search of data?

If I had to guess, I would guess that querying the database would result in lower load. The problem is that you're probably going to write an inefficient implementation to simulate what the relational database engine does (and this is also due to how the data is organised with a CSV or XML format compared to the structures used in a relational database storage format, along with the fact that you will probably not implement any kind of indexing). Inefficiency => more resources needed to solve the problem.

wyattstorch42 wrote:
If I use MySQL, do I have to develop a caching system or something?

An image gallery is rather dynamic in nature, unlike say, a page that serves daily announcements, so caching might not help very much.

wyattstorch42

Thanks for the advice.

Now I've been asked to avoid using server-side scripting altogether. Which I'd rather not, but I suppose it's possible with AJAX. I don't like the idea of relying so heavily on JavaScript, though. And that means generating thumbnails will have to be done with a local application. The only experience I have in that is basic Java, which I haven't done in over a year. Back to OOP, I guess!

But you're right. I didn't think of there being no indices, so it must be slower to run through an array. And I know how much of a pain it would be to maintain an artificial database.

dagon

no server-side, how very odd, and a bad idea, why?

wyattstorch42

dagon;10970572 wrote:
no server-side, how very odd, and a bad idea, why?

I'm not entirely sure. I think he hasn't fully decided on a platform running PHP or ASP, because he has applications of either, and installing both is too complicated for him apparently. I just do what he says.

But I'm not enthusiastic about not using PHP to do this, since it seems to make the most sense in this situation. However, I'm now looking into generating thumbnails via Java on a local computer, which is kind of nice because generating a thousand images via PHP can be a pain, especially if your host has a tendency to force operations to time out.

dagon

ok if the java is going to run on the clients machine, but i wouldn't want to be a user of the gallery and have to download a Java app to submit to it (or worse view). You cant know what client side technologies any visitor can or will run, so its a lot harder than the known certainties of client side.

wyattstorch42

dagon;10970663 wrote:
ok if the java is going to run on the clients machine, but i wouldn't want to be a user of the gallery and have to download a Java app to submit to it (or worse view). You cant know what client side technologies any visitor can or will run, so its a lot harder than the known certainties of client side.

Yeah, it would be only a local application. The Java program generates the thumbnails and builds an XML file that contains a complete list of images and albums, regardless of whether or not they have descriptions. Then the client browser reads the XML with AJAX and generates the HTML for the gallery. No PHP involved.

Of course, I've never done something as advanced as making thumbnails in Java, and Java in general is kind of a pain in comparison to PHP in my opinion. I like OOP, but sometimes it seems like it takes 50x the amount of work to accomplish the same process.