Database schema for To Do List

[MatazaNZ]

Hi all,

 

It's been a good long while since I've worked with databases, and I've only really worked on them on a very very basic level. I'm now starting to learn to write programs that can interact with databases, and for a beginning task, I want to write a To Do application. I've got a very basic schema for the To Do tasks, but I would like any input on what I could do to make it better. I'm not sure where I could go from here for normalisation. 

 

Basically, each task will have an ID (auto-incremented), a short description, due date, a repeat type (which also includes non-repeating), and whether it's complete or not. Each task can also have a list of items associated with it. This is my basic schema here

 

 

A lot of the logic about whether the task is overdue, time left until the due date, etc will be left to the application to calculate.

 

Any suggestions to what I could add or change are welcome

 

Thank you

[thekeemo]

Are you asking for how to implement or what else to implement?

[MatazaNZ]

21 minutes ago, thekeemo said:

Are you asking for how to implement or what else to implement?

What else to implement, mostly. Whether this schema is good, and if it needs anything adjusted to make things easier in the long run

[thekeemo]

1 hour ago, MatazaNZ said:

What else to implement, mostly. Whether this schema is good, and if it needs anything adjusted to make things easier in the long run

id for task items

[vorticalbox]

6 hours ago, thekeemo said:

id for task items

if task id is unique then why make another field?

 

so from I read you're creating a task such as home work then also creating items for that task? I

 

f so you might want implement predecessor allowing one to set an order of tasks that must be completed. E.G research on topic must be done before you can write the report.

[MatazaNZ]

3 hours ago, vorticalbox said:

if task id is unique then why make another field?

 

so from I read you're creating a task such as home work then also creating items for that task? I

 

f so you might want implement predecessor allowing one to set an order of tasks that must be completed. E.G research on topic must be done before you can write the report.

I'm torn on whether to make a PK field for the task_items table. I'm not sure if I should make an ID field or not, as each task id can be linked to multiple task items, unless I make a new column in task_items as a step number, and another column for whether or not the steps must be done in order. Then the task_id field and step number field can be used together to form the PK. Then using the new fields, that would then allow me to implement what you suggested. The homework item is one example (not one I would use, as I don't need to do homework anymore). Another example as a repeatable one could be housework. Set up a task for weekly/monthly housework, which one could check off, and at each repeat, the items would be reset to not complete, and those items could be done in any order. 

 

So this would be the updated schema

 

[vorticalbox]

13 minutes ago, MatazaNZ said:

I'm torn on whether to make a PK field for the task_items table. I'm not sure if I should make an ID field or not, as each task id can be linked to multiple task items, unless I make a new column in task_items as a step number, and another column for whether or not the steps must be done in order. Then the task_id field and step number field can be used together to form the PK. Then using the new fields, that would then allow me to implement what you suggested. The homework item is one example (not one I would use, as I don't need to do homework anymore). Another example as a repeatable one could be housework. Set up a task for weekly/monthly housework, which one could check off, and at each repeat, the items would be reset to not complete, and those items could be done in any order. 

 

So this would be the updated schema

 

Seeing as a task can have lots of items it is probally better to give them an ID, seems @thekeemo is right after thinking about it. I don't have homework either but it was something everyone is familiar with so that's why i used it. 

 

looks good to me, If you build your app correctly it actually shouldn't matter what you do to the database the app should work with little to no changes. Will say i'm not great with databases, well mostly SQL never actually used joins and what not before but it's on the to learn list

[MatazaNZ]

33 minutes ago, vorticalbox said:

Seeing as a task can have lots of items it is probally better to give them an ID, seems @thekeemo is right after thinking about it. I don't have homework either but it was something everyone is familiar with so that's why i used it. 

 

looks good to me, If you build your app correctly it actually shouldn't matter what you do to the database the app should work with little to no changes. Will say i'm not great with databases, well mostly SQL never actually used joins and what not before but it's on the to learn list

In my software development diploma, we learned about SQL, and did a module on designing and building a database, but those were mostly with MS Access and SQL Server. I had to design a database for a library with book rentals and such.  But I've lost a lot of what I learned. Working on building that back up,  as well as integrating that with applications, rather than just working with management tools. Some practical, end user applications, you know, that sort of thing. This should hopefully be something to go on, and if I get a good, working application, expect to see it posted here

[vorticalbox]

51 minutes ago, MatazaNZ said:

In my software development diploma, we learned about SQL, and did a module on designing and building a database, but those were mostly with MS Access and SQL Server. I had to design a database for a library with book rentals and such.  But I've lost a lot of what I learned. Working on building that back up,  as well as integrating that with applications, rather than just working with management tools. Some practical, end user applications, you know, that sort of thing. This should hopefully be something to go on, and if I get a good, working application, expect to see it posted here

will look forward to seeing it. I finished a degree and had a database applications unit. I didn't learn any of the del stuff and passed with a B2. Was all theory mostly. I still have all the files so might have to look over them again.

 

if you need any help with the application do post.

[mathijs727]

1 hour ago, MatazaNZ said:

I'm torn on whether to make a PK field for the task_items table. I'm not sure if I should make an ID field or not, as each task id can be linked to multiple task items, unless I make a new column in task_items as a step number, and another column for whether or not the steps must be done in order. Then the task_id field and step number field can be used together to form the PK. Then using the new fields, that would then allow me to implement what you suggested. The homework item is one example (not one I would use, as I don't need to do homework anymore). Another example as a repeatable one could be housework. Set up a task for weekly/monthly housework, which one could check off, and at each repeat, the items would be reset to not complete, and those items could be done in any order. 

 

So this would be the updated schema

 

I think it might make sense to store the ID of the successor/predecessor (like a linked list).

That would make insertion/deletion of new task items easier.

[MatazaNZ]

10 minutes ago, mathijs727 said:

I think it might make sense to store the ID of the successor/predecessor (like a linked list).

That would make insertion/deletion of new task items easier.

How would you mean by this? 

[vorticalbox]

rather than just order, have it stop successor and predecessor.

[Dat Guy]

I find the whole project too bloated. A Todo list is much too easy for a full-blown DBMS.

[MatazaNZ]

1 hour ago, Dat Guy said:

I find the whole project too bloated. A Todo list is much too easy for a full-blown DBMS.

That's not the point. Too easy or not, this is to get me back into working with databases, then I can start to ramp up difficulty. For the time being, and into the foreseeable future, I'm using SQLite, rather than a full on database server. 

[Bigun]

Sounds like you're asking for best practice advise?  Is that correct?

[MatazaNZ]

On 19/08/2016 at 6:02 AM, Bigun said:

Sounds like you're asking for best practice advise?  Is that correct?

Nor specifically, but I'll always accept best practice advice, especially if it's in the context of the schema I'm working on

Edited August 21, 2016 by MatazaNZ

[Bigun]

• Always have unique identifier for every record in every table
• Ensure primary keys are unique
• Avoid having to use multiple items as keys, if you get cornered into such a practice, combine these items into a field, forming your unique key
• Seek out and normalize your data where it makes the most sense.  If you have to break your customer's legs to get them to admit it, then do it.  Nothing is more aggravating than having to normalize a table post-production.
• Unsure if your in charge of the frontend for this beast, but if you are, try to use tables for drop down elements as well.  Makes things a lot easier in the future.

Off the top of my head, that's all I can remember.  Here is another article I found whilst googling with some good advice, some of it I've already spouted.

[MatazaNZ]

On 21/08/2016 at 7:17 PM, Bigun said:

• Always have unique identifier for every record in every table
• Ensure primary keys are unique
• Avoid having to use multiple items as keys, if you get cornered into such a practice, combine these items into a field, forming your unique key
• Seek out and normalize your data where it makes the most sense.  If you have to break your customer's legs to get them to admit it, then do it.  Nothing is more aggravating than having to normalize a table post-production.
• Unsure if your in charge of the frontend for this beast, but if you are, try to use tables for drop down elements as well.  Makes things a lot easier in the future.

Off the top of my head, that's all I can remember.  Here is another article I found whilst googling with some good advice, some of it I've already spouted.

Back and front end of this are entirely me. For details, I'm doing this using C# and SQLite. Mostly for learning interaction with databases in .NET, while also making myself some programs to help my personal productivity