Database schema for a ToDo list

databasedatabase-design

I am trying to make a very simple todo list application with PHP, MySQL, Jquery templating and JSON… However, my schema seems to complicate things in JSON.

What's the best way to do it?

  1. A new table for each list, containing the items.

or

  1. a table for lists, and a table for items that are joined somehow? Because I have tried this and it doesn't seem like the right way to do it? Example http://jsfiddle.net/Lto3xuhe/

Best Answer

There's a joke I heard awhile back:

Q How does a BASIC coder count to 10?
A 1,2,3,4,5,6,7,8,9,10

Q How does a C coder count to 10?
A 0,1,2,3,4,5,6,7,8,9

Q How does a DBA count to 10?
A 0,1,many

The truth behind this joke is that once you have two (or more) of the same thing in a database structure (columns or tables), you're doing it wrong.

A schema that looks like:

+----------+
| id       |
| name     |
| phone1   |
| phone2   |
|          |
+----------+

Is wrong because where will you put a third phone number if someone has it?

The same applies to tables themselves. Its also a Bad Thing to be modifying the schema at runtime, which the "new table for each list" seems to imply. (Related: MVC4 : How to create model at run time?)

And thus, the solution is to create a todo list that is comprised of two tables. There are two things you have - lists and items.

So, lets make a table structure that reflects this:

+----------+       +-------------+
| List     |       | Task        |
+----------+       +-------------+
| id (pk)  <---+   | id (pk)     |
| name     |   +---+ listid (fk) |
|          |       | desc        |
|          |       |             |
+----------+       +-------------+

The list has an id (the primary key for the list), and a name. The task has an id (the primary key) a listid (a foreign key) and the description of the task. The foreign key relates back to the primary key of another table.

I will point out that this doesn't begin to encompass all the possibilities in various requirements for the software and the table structure to support it. Completed, due date, repeating, etc... these are all additional structures that will likely need to be considered when designing the table. That said, if the table structure isn't one that is appropriately normalized (or realizing the tradeoffs that you've made because it's not normalized), you will have many headaches later.

Now, all that relates to writing this as a relational database. But thats not the only type of database out there. If you consider a list to be a document the document styled nosql databases may also offer an approach that isn't wrong.

While I'm not going to delve into it too far, there are numerous tutorials out there for todo lists in couch. One such that came up with a search is A simple Task-list application in CouchDB. Another shows up in the couchdb wiki: Proposed Schema For To-Do Lists.

In the approach appropriate for a couch, each list is a JSON document stored in the database. You would just put the list in a JSON object, and put it in the database. And then you read from the database.

The JSON could look like:

[
 {"task":"get milk","who":"Scott","dueDate":"2013-05-19","done":false},
 {"task":"get broccoli","who":"Elisabeth","dueDate":"2013-05-21","done":false},
 {"task":"get garlic","who":"Trish","dueDate":"2013-05-30","done":false},
 {"task":"get eggs","who":"Josh","dueDate":"2013-05-15","done":true}
]

(from creating a shopping list with a json file on Stack Overflow).

Or something approaching that. There is some other record keeping that couch has as part of the document.

The thing is, its not the wrong way to approach and a todo list in a document database may be perfectly suited to what you are trying to do with less concept overhead for how to do it.

Related Topic