Better to store in DB or in file?

Is it better to have 35kbs of text stored in a single file to be used as an include or to store it in the sites main DB.

I would imagine that while the DB is rather small it would be better in the DB, but what if the DB gets quite a few records, in the 1000's or even 10's of thousands or more with each having roughly the same amount of data of around 35kb's each?

Just curious to work out what way is best to go

Thanks

GW

Text files are called "flat" files because the data is accessed in a very basic way. You read all of the data then any operation aside from just displaying it is difficult. A database does exactly the same thing, but it ALSO allows you to organize, search, cross-reference, add/remove, edit, and do many other things with the data easily. Essentially a database is a set of structured "flat files" stored in a way that you can easily order. So you could mimic it using hundreds of text files (one for each cell in the database), but obviously that defeats the point.

In other words, the size of the data is irrelevant (obviously it's better to keep image files as .jpg rather than putting them in a database), but it's about what you do with them and whether you are accessing them in a simple or complex (especially unpredictable) way.

One way to get around a bit of the limitations of text files is to use several files, such as one per page. But once you get beyond that, it's time to start using a database.


Certainly if you have many "records" then you already are using a primitive database. It's always better to use a real DB than to try to fake one with text files. If you are actually just using text files to store data "as is" and put it into a page, that's fine.

In fact, it's best to keep highly organized small amounts of data in a database and keep everything bigger outside it. This improves speed. Of course it's not always possible to be optimal in every way, but keep that in mind as a general guideline.


Here's an example: I just built a wiki, and among other things that means storing articles. The way that I did it was to create a database with a table called 'articles' and to insert data into that including the username, the time, the title, etc. But I did not store the full text of the article there. Instead, I stored it in a text file, and kept the ID (filename) of this text file in the database. So instead of a field called 'text' I had a field called 'id'. Of course this is much more complex than a lot of systems, but once you start working with a lot of data the details become important.

To be honest, what you're describing sounds like a minimal amount of data, so I'd suggest using whatever is easiest for you to program.

Hi Daniel, thanks for the reply.

Ok what I am using is ZenPhoto, but as part of that solution I have a HTML block of text, links and images links that will go with about 1/3 of all image in the system (20,000 so far). Basically your stock standard web-page content as if it was a static page

Within this HTML block there are also javascript actions that do a variety of things. That block of HTML when I look at its file size is about 35Kbs in size, nothing massive but still a fair bit for a single page which is on top of the already generated page done by Zenphoto. I hope I am making sense to you :)

What I have been doing is adding that HTML block as code in ZenPhotos codeblock, so therefore it is stored in the DB and then as the page is generated it calls for the HTML in that codeblock and displays it. Just as if I had an include in a html page.

So this is where I was trying to work out what is better longer term for the system, to continue to insert this HTML block in the DB for each photo that needs it or to do a html include via the template system and therefore have the html as a static file that is pulled into the generated page.

My thinking was that the page is already being generated from the DB via a file that is the template, so the HTML block might as well come from the DB as well, but then I thought about the DB size, i.e. will it be a long term issue or not?

Hence my question :)

Cheers

GW

Ok, that is a different situation from what I understood.

35kb is a lot if that is for every entry and you have thousands of them. It isn't horrible, but it could be more efficient if you eliminate that.

The best way to do it is to have this code as a template and leave variables in it. Then in your database store the values for the variables. The Javascript especially should be stored separately.

An important rule for databases is this: Never duplicate information. This is good advice both for efficiency and for management: if you ever change the code you will need to edit every entry, or just edit one template. Obviously the template is much easier to maintain.
(Of course this isn't absolutely necessary if this project is working well, but it's a good idea for the future. At this point, for this project, will it save you time?)

Thanks Daniel for your info and advice. Now I just need to learn how to store the variables in the DB and how to call on them as needed :)

I can certainly create a template out of the HTML being used, I would guess 90% of it would be common and therefore part of a template, the other 10% would be the variables as you say for the content.

Can I store the variables as one block of content in each record and then use javascript to simply get those variables and then apply them to that page?

Is there a limit to how big a variable can be?

My thinking is that I can store the variables in the field and when the page is called the javascript can pull those variables as a block and then I can apply them where they are needed with additional javascript.

Time to have a play I think, but if you have a suggestion or two how to do this (beginner style) please let me know :)

Thanks for your help

Javascript won't be relevant (yet). You'll be using PHP or another server side language (your choice) to gather the variables from the database. You'll get the relevant row from the table of your entries. That row will have a few cells, each with one 'variable'. Take that value, insert it into the correct location in your template, and that's all. Generally speaking there's no limit on the length of a variable, though depending on the language and other things there might be something (in most cases probably at the level of gigabytes). Note that mysql fields can be set to a certain length, so that does give a limit, but if you expect long data you can always use a bigger limit.

If you want to know more about mysql and you are using php, then I recommend this tutorial:
http://php-mysql-tutorial.com