[Tutor] Proper SQLite cursor handling?

[Alan Gauld]

On 28/07/2021 17:19, Dennis Lee Bieber wrote:
> On Sun, 11 Jul 2021 18:34:01 -0400, Dennis Lee Bieber

> Codd's opinion, NO field should allow NULL values. That implies that the
> relevant fields are to be pulled out into a subordinate table, with a
> unique foreign key to ensure that there are only 0 (the former NULL value)
> or 1 record linked back to the parent record.

That may be the purist view (or maybe just Codd!) but from a pragmatic
view introducing new tables with a single value linked to from just one
other table is a maintenance and performance nightmare.

Plus it still begs the question about how you represent NULL values -
thee are real world scenarios where things are optional and so can
be NULL. You have to either invent spurious default values or some
other way to fake it if using NOT NULL columns.

> 	As a result, I've hacked my SQLite3 test scripts. One result is that
> the BEFORE and AFTER UPDATE triggers are now gone -- the subordinate table
> can have a default timestamp for datetime column, and NOT NULL will trap a
> missing score.

There may be a few cases where you are dealing with dynamic updates,
but apart from simplistic databases like SQLite I still prefer
writing a stored procedure or trigger to deal with those.

> -- Doing this split does complicate the JOINs needed to retrieve data,
> -- but also removed the need for UPDATE triggers to ensure end time
> -- and score are filled in, as a default time can be specified, 
> -- and NOT NULL traps missing score. 

The avoidance of update triggers may cancel the performance penalty of
the join. But it doesn't cancel the potential added complexity of all
the selects, deletes, updates etc. which now have to manage two tables
rather than one making them more fault prone.

Its conceptually similar to the questions you must ask in OOP when
determining when to create a new class for an attribute of an
object. Does the added abstraction provide more benefit than
the added complexity of maintaining two classes? Its the same
debate with tables.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos