CODEWITHSUNDEEP
sqlsqlite
Silve2611
Silve2611

Reputation: 2278

View vs Tables in SQLite

I have a Table where I save daily data TDay. 

Task    | Time  | Day | Month | Year |
--------------------------------------
Cooking | 20min | 21  | 03    | 2015 |
Reading | 20min | 21  | 03    | 2015 |
Smiling | 20min | 21  | 03    | 2015 |
Washing | 20min | 21  | 03    | 2015 |
Cooking | 20min | 22  | 03    | 2015 |
Reading | 20min | 22  | 03    | 2015 |
Smiling | 20min | 22  | 03    | 2015 |
Washing | 20min | 22  | 03    | 2015 |
Watching| 20min | 01  | 04    | 2015 |
Washing | 20min | 01  | 04    | 2015 |
Reading | 20min | 01  | 04    | 2015 |
Ironing | 20min | 01  | 04    | 2015 |

Now i would need a VIEW or a Table TWeek and TMonth that gathers those results

TWeek

Task    | Time  | Week | Year |
-------------------------------
Cooking | 40min | 12   | 2015 |
Reading | 40min | 12   | 2015 |
Smiling | 40min | 12   | 2015 |
Washing | 40min | 12   | 2015 |

TMonth

Task    | Time  | Month | Year |
-------------------------------
Cooking | 40min | 03    | 2015 |
Reading | 40min | 03    | 2015 |
Smiling | 40min | 03    | 2015 |
Washing | 40min | 03    | 2015 |
Watching| 20min | 04    | 2015 |
Washing | 20min | 04    | 2015 |
Reading | 20min | 04    | 2015 |
Ironing | 20min | 04    | 2015 |

So what I am doing is to gather the results daily and sum the up for a week and a month. The Problem is that I am expecting a lot of data.

Should I use views for that or should I write a small programm that fills tables? Or is it smarter to make a Select where I sum up the data? In the and I will represent a result.

Upvotes: 3

Views: 1747

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The decision on whether to use a view or summary tables is entirely dependent on your performance needs. In either case, you can get the system to work.

With a view, you implement the logic one time. For instance:

select task, sum(time) as time, month, year
from tday
group by task, month year

for the second summary.

With summary tables, you need to keep the data up-to-date. Perhaps you can do this when loading the data. More likely, you will need a separate job or triggers on the table to maintain consistency.

The additional work load for summary tables, along with the increased difficulty of maintaining the system, are why views are often preferred. Some databases support a construct called materialized view, but I don't think SQLite has an equivalent construct.

A typical approach is to start with views. If you have performance problems, then investigate alternatives. Materializing the summaries is one common solution.

Upvotes: 4

Related Questions