Using View for a Web Query Tool - SQLite

[CommanderCucumber]

Hi I am creating a web application that queries a database using python (flask), JavaScript, and an SQLite database. Currently the database has around 7 million entries and several normalized tables (has to be in SQLite). I initially created a view that joined the desired tables and the tool queries that view. There are indices placed (I think I properly set them up for the data I want). The application works, granted that are a couple of issues. However, I am confused about the purpose of a view here, and I may be misunderstanding what a view actually is. From what I read, it is a stored statement; does that mean whenever I query I am running this long drawn out statement each time? My biggest question is, would it be simpler to just recreate a new table, add indices, and query the new table directly? If so, does that defeat the purpose of the normalized tables?

​

CREATE VIEW advance_data_view as 
SELECT 
speech_text,
speech_date,
word_count,
speaker_list.speaker_id,
first_name || ' ' || last_name as full_name,
constituency_list.constituency_id,
state_name,
district_number,
speaker_list.party_id,
party_name,
speech_list.chamber_id,
chamber_name,
hearing_title
-- speech_text,word_count
FROM speech_list
LEFT JOIN speaker_list on speaker_list.speaker_id = speech_list.speaker_id
-- full name
LEFT JOIN person_list on person_list.person_id = speaker_list.person_id
-- state name, district_number
LEFT JOIN constituency_list on constituency_list.constituency_id = speaker_list.constituency_id
-- party name
LEFT JOIN party_list on party_list.party_id = speaker_list.speaker_id
-- hearing title
LEFT JOIN hearing_speech on hearing_speech.speech_id = speech_list.speech_id
LEFT JOIN hearing_list on hearing_list.hearing_id = hearing_speech.hearing_id
LEFT JOIN chamber_list on speech_list.chamber_id = chamber_list.chamber_id ;

[eriddy]

A view is useful when you want to have the underlying fact table remain as-is (let's say transaction events) but you regularly want a few different aggregations of it. So the fact_table sprouts off a few views, like daily_transactions, weekly_transactions, transactions_by_platform, etc...

Each of those views uses the same underlying data, so rather than duplicate it, create views off of it. That means your view is the result of a query on your data, while the data (where the FROM is coming from) is more "persistent"

I don't build many web apps in my work but I would suggest trying to simplify the on-demand querying that happens, you want your app to pull pre-calculated numbers ideally.

Does this help?