G'day everyone, welcome back and welcome to section 15. In this section, we're going
to add the Timings table to our database, and get our app to record the time spent
on the tasks. We've already got a database, and if we create a new one
we'll lose all the users' data. Now, I know the app hasn't been released yet, but
there will come a time when you want to modify the structure of a database that
is in a live application. In this section, we'll see how to do that. In fact, we're
gonna do it twice - we'll do it again in the next section as well. There's a
reason for that. Upgrading from one database version to
the next version isn't the same as upgrading an old database version to one
that's 2 or 3 versions further on. Remember that users may not update your
app straight away, and you have to cater for that. The reason we'll be upgrading
the database in this section, is because we have to add another table. We need to
store the timings for the tasks. That gives us a chance to see how to work
with more than one table in our app. We've allowed the user to delete a task.
When that happens, they won't want to have all the timings for that task
remaining in the database. That's very common - deleting a parent record should
remove any child records as well. We'll see how to use database trigger to do that.
It keeps our code simpler, and it's also
more reliable than trying to delete child records in code.
I'll finish this video by reviewing the structure of our database. We've already
created the Tasks table. At the moment, that's the only table our database
contains. We'll be adding a Timings table in this section. The rows in the Timings
table are linked to the Tasks in the Task table by the TaskId column. We can
use a WHERE clause to find all the timings records for a particular task.
We can also use a JOIN, and we'll do that to get the data for the reports.
Each task can have many Timings records. A new row is added to the Timings table,
each time a user starts timing a Task. We'll be storing the StartTime, when a
user starts timing a task. When they stop timing it, the Duration column is
updated with the total duration for that particular Timing. Doing it this way
means we're not running a timer. A new row is added to the database, with the
current StartTime, when a task starts to be timed. When the user stops timing it,
the database is updated with the Duration. The phone can be off, and the correct
duration will be saved when it's turned back on again, and the timing stopped.
Our users may work in an environment where mobile phones aren't allowed. This lets
them use the app, even in places like a hospital - the phone can be turned off
for the duration of the task. We will have to remember which task was being
timed, when the app starts up again. We'll look at ways to do that, in this section.
Data for the reports will come from a view that links the Tasks and Timings
tables, using a JOIN. We'll create vwTask Durations in the next section. We could
create it now - we know that we're going to need it, after all. That's probably
what you would do. In fact, you'd probably have created the entire database schema
right at the start. I'm doing it this way so I can show you how to upgrade a
database. It's a bit artificial, here, because we're adding things that we
already know we're going to need, but in the real world, new requirements will
mean that you have to change your database schema from time to time.
I'm pretending to "forget" that we need the Timings table and the view, to
demonstrate how to apply successive updates to our database. Alright, that's
what our database is going to look like In the next video, we'll upgrade the
database and add the Timings table.