Database schema for a ToDo list

Question

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

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

• user40980: How many lists will you be looking to support?
• CodeSlow: Maximum of 100. What are the limits?
• user40980: The way a dba counts. A normal person counts to ten as "1,2,3,4...10". A C programmer counts to ten as "0,1,2,3,...9". A dba counts "zero, one, many."

Answer 1

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.

• Haris: This is a great answer! but some of the links no longer exists

Answer 2

Option 2 is a traditional master/detail setup. That is probably what you want here. Put the list id in the items table, and join on that. The schema shouldn't impact the JSON. Your query might look something like:

select lists.name as list_name, items.name as item_name 
from items 
join lists on (lists.id = items.list_id)

• CodeSlow: Getting this error: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given?
• FrustratedWithFormsDesigner: @CodeSlow: That's a specific, detailed code question, more appropriately asked on stackoverflow.com

Answer 3

I wouldn't try to tie your UI representation or transmission of data to the UI directly to how you intend to store the data. By keeping the two separate and using some middleware logic to marry the two allows you to easily alter either side without possibly impacting the other in a critical way.

From a data storage perspective, you would likely use option 2 which follows the typical normalized data pattern where common parts are factored out into their own tables to avoid repetition and to minimize database bloat.

From a view perspective, you simply need to use a database query to join the pertinent data into a result set and then iterate that result and generate a json response applicable to your UI needs. What you likely want to do is feed the data into JSON so that it fits your UI needs as best as possible, often eliminating the need for additional scripting logic in your web pages.

• CodeSlow: This is exactly what I need to do , but no idea how to go about doing it. Do you have any example material I can have a look at/follow? Thanks - Generating JSON in PHP