3

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...

Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
Michael Stachowsky
  • 141
  • 4
  • related: [Why use a database instead of just saving your data to disk?](http://programmers.stackexchange.com/questions/190482/why-use-a-database-instead-of-just-saving-your-data-to-disk) – gnat Jul 07 '16 at 13:42
  • 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 07 '16 at 13:48
  • 2
    This is actually a very commonly asked question. See [here](http://programmers.stackexchange.com/search?q=store+files+in+database) and [here](https://www.google.com/search?q=store%20files%20in%20database). – Robert Harvey Jul 07 '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 07 '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 07 '16 at 14:03
  • I understand, you make a good point. Eventually I want to be able to do something where there is data stored in the database (like the shopping list itself) and now I see that it makes much more sense to save that data in the db tables in the first place. However, I also want to be able to upload files to the server for various reasons (after the shopping list project). The second project will involve data like lists, so definitely that goes into the db, but then also user-created files. What happens with this mixed data? – Michael Stachowsky Jul 07 '16 at 14:18
  • Did you have a look at some of those articles I linked? – Robert Harvey Jul 07 '16 at 14:25
  • I did, but it seems that the answer is yes to both? Here is what I think I have understood: in my particular case, the files will be nothing more than text files no more than a few KB in length, and used by no more than a few hundred users per year. This being the case, for data integrity and atomicity, it makes much more sense to store the files in the db as BLOBs. Did I more or less get that right? – Michael Stachowsky Jul 07 '16 at 14:36

2 Answers2

3

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).
Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
  • 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 07 '16 at 14:49
  • @DavidPacker Expanded the point to bring attention on the 304 caching issue. Thnx. – Tulains Córdova Jul 07 '16 at 14:57
0

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.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172