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…
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:
- 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).
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.