1
00:00:03,840 --> 00:00:10,120
G'day everyone, welcome back and welcome to
section 15. In this section, we're going

2
00:00:10,120 --> 00:00:14,889
to add the Timings table to our database,
and get our app to record the time spent

3
00:00:14,889 --> 00:00:19,870
on the tasks. We've already got a
database, and if we create a new one

4
00:00:19,870 --> 00:00:25,750
we'll lose all the users' data. Now, I know
the app hasn't been released yet, but

5
00:00:25,750 --> 00:00:30,250
there will come a time when you want to
modify the structure of a database that

6
00:00:30,250 --> 00:00:36,850
is in a live application. In this section,
we'll see how to do that. In fact, we're

7
00:00:36,850 --> 00:00:41,140
gonna do it twice - we'll do it again in
the next section as well. There's a

8
00:00:41,140 --> 00:00:44,770
reason for that.
Upgrading from one database version to

9
00:00:44,770 --> 00:00:49,870
the next version isn't the same as
upgrading an old database version to one

10
00:00:49,870 --> 00:00:55,570
that's 2 or 3 versions further on.
Remember that users may not update your

11
00:00:55,570 --> 00:00:59,500
app straight away, and you have to cater
for that. The reason we'll be upgrading

12
00:00:59,500 --> 00:01:04,930
the database in this section, is because
we have to add another table. We need to

13
00:01:04,930 --> 00:01:09,009
store the timings for the tasks. That
gives us a chance to see how to work

14
00:01:09,009 --> 00:01:14,130
with more than one table in our app.
We've allowed the user to delete a task.

15
00:01:14,130 --> 00:01:18,369
When that happens, they won't want to
have all the timings for that task

16
00:01:18,369 --> 00:01:24,219
remaining in the database. That's very
common - deleting a parent record should

17
00:01:24,220 --> 00:01:29,980
remove any child records as well. We'll
see how to use database trigger to do that.

18
00:01:29,980 --> 00:01:32,319
It keeps our code simpler, and it's also

19
00:01:32,320 --> 00:01:36,540
more reliable than trying to delete
child records in code.

20
00:01:36,540 --> 00:01:43,119
I'll finish this video by reviewing the
structure of our database. We've already

21
00:01:43,119 --> 00:01:48,609
created the Tasks table. At the moment,
that's the only table our database

22
00:01:48,609 --> 00:01:55,689
contains. We'll be adding a Timings table
in this section. The rows in the Timings

23
00:01:55,689 --> 00:02:02,079
table are linked to the Tasks in the
Task table by the TaskId column. We can

24
00:02:02,080 --> 00:02:06,600
use a WHERE clause to find all the
timings records for a particular task.

25
00:02:06,600 --> 00:02:13,000
We can also use a JOIN, and we'll do that to
get the data for the reports.

26
00:02:13,000 --> 00:02:18,540
Each task can have many Timings records.
A new row is added to the Timings table,

27
00:02:18,540 --> 00:02:23,890
each time a user starts timing a Task.
We'll be storing the StartTime, when a

28
00:02:23,890 --> 00:02:28,810
user starts timing a task. When they
stop timing it, the Duration column is

29
00:02:28,810 --> 00:02:34,240
updated with the total duration for that
particular Timing. Doing it this way

30
00:02:34,240 --> 00:02:39,070
means we're not running a timer. A new
row is added to the database, with the

31
00:02:39,070 --> 00:02:45,420
current StartTime, when a task starts to
be timed. When the user stops timing it,

32
00:02:45,420 --> 00:02:50,290
the database is updated with the Duration.
The phone can be off, and the correct

33
00:02:50,290 --> 00:02:55,300
duration will be saved when it's turned
back on again, and the timing stopped.

34
00:02:55,300 --> 00:02:59,650
Our users may work in an environment where
mobile phones aren't allowed. This lets

35
00:02:59,650 --> 00:03:03,430
them use the app, even in places like a
hospital - the phone can be turned off

36
00:03:03,430 --> 00:03:08,410
for the duration of the task. We will
have to remember which task was being

37
00:03:08,410 --> 00:03:15,600
timed, when the app starts up again. We'll
look at ways to do that, in this section.

38
00:03:15,600 --> 00:03:19,660
Data for the reports will come from a
view that links the Tasks and Timings

39
00:03:19,660 --> 00:03:27,250
tables, using a JOIN. We'll create vwTask
Durations in the next section. We could

40
00:03:27,250 --> 00:03:31,780
create it now - we know that we're going
to need it, after all. That's probably

41
00:03:31,780 --> 00:03:36,190
what you would do. In fact, you'd probably
have created the entire database schema

42
00:03:36,190 --> 00:03:41,080
right at the start. I'm doing it this way
so I can show you how to upgrade a

43
00:03:41,080 --> 00:03:46,030
database. It's a bit artificial, here,
because we're adding things that we

44
00:03:46,030 --> 00:03:50,950
already know we're going to need, but in
the real world, new requirements will

45
00:03:50,950 --> 00:03:55,000
mean that you have to change your
database schema from time to time.

46
00:03:55,000 --> 00:04:00,220
I'm pretending to "forget" that we need the
Timings table and the view, to

47
00:04:00,220 --> 00:04:05,260
demonstrate how to apply successive
updates to our database. Alright, that's

48
00:04:05,260 --> 00:04:09,760
what our database is going to look like
In the next video, we'll upgrade the

49
00:04:09,760 --> 00:04:14,100
database and add the Timings table.