I'm trying to better understand how to use a database in a personal website. I have a set of lists, like my shopping list, to-do list etc. Currently, they are all stored in text files with hard-coded links. I want to start learning database stuff with this simple problem: I want to be able to store my lists somehow, then retrieve them from the server without having to hard code my links.

My question is: is my database supposed to contain the links to the files, or would it contain the files themselves? If it stores the links, then am I supposed to create a web application that lets me make a list, store it on the server, and then run a server side script to update the db with the new information? Sorry for the newbie question...

  • Interesting. Now, this somewhat trivial example I gave is really just there so I can learn databases. From that post, it looks like my data is pretty trivial, but I still want to use the db to better understand how to set one up for more complicated projects. Are you saying that it might just make more sense to have a file that stores the file names and paths? – Michael Stachowsky Jul 7 '16 at 13:48
  • 2
    This is actually a very commonly asked question. See here and here. – Robert Harvey Jul 7 '16 at 13:51
  • Re, "is my database supposed to contain the links to the files, or would it contain the files themselves?" Your files are containers for data. A DBMS (database management system) is a container for data. You can put your files into a DBMS as BLOBs (Binary Large OBjects), but the DBMS will be much more useful to you if you think in terms of storing the data in it. – Solomon Slow Jul 7 '16 at 13:53
  • Do you want to model the shopping list using your database, e.g. what items constitute a shopping list? What attributes does a shopping list item have? and so on. Or do you want to treat the shopping list as "collection of bytes" that is not understood by the database but just stored there. – Brandin Jul 7 '16 at 14:03

Given the fact that your files are just text files containing text lines, my suggestion is this:

  • Don't save links to the files
  • Don't save the files themselves
  • Save the list items to the database

Here's a simple physical model to store lists:

enter image description here

  • In the case we were talking about large files like video files or large images, it's better to just store the URI/URL to the image.
  • For small images storing them as blobs in the database is not a bad idea, but I'd suggest to put them in a separate table with a one-to-one relationship so you can store them in a separate tablespace. In this case consider issues related to how web browsers cache images (see @david-packer 's comment bellow).
  • I don't think if it has changed, but when pulling images from database, the 304 Not Modified functionality is not available by default and must be taken care of by the programmer. Take that into consideration when deciding whether to store an image in a database or not. – Andy Jul 7 '16 at 14:49
  • @DavidPacker Expanded the point to bring attention on the 304 caching issue. Thnx. – Tulains Córdova Jul 7 '16 at 14:57

While you can save files in the DB, or save links to the files.. this is not what you want here. SQLServer even has the feature of linking the files on disk into its tables so the fies on disk appear to be part of the DB directly.. but that's pretty advanced usage.

What I think you want to do is to store the data that you currently have in text files into the DB. From there you can write a program that can re-create the text files from the DB data. That's the best way to store things, you get all the advantages of a DB with regard to reporting and editing, and also have the ability to save the data to files (and, go crazy - load them too).

So you will want, off the top of my head, a table to hold all the lists, and then another to hold all the items in those lists. If the list-item table has a column that says which list it belongs to, you can easily recreate a list with SQL like "select * from list-items where list-id = x" where x is the list you want to get (eg 'tuesday shopping list', or 'DIY TODO' list).

From there, it only gets better - add more and more flexibility to add new lists, more data options, datetime columns to mark entries as completed or when they have to be completed by.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.