On 2020/02/23 21:23, Richard Damon wrote:
On 2/23/20 8:31 AM, Olaf Schmidt wrote:
An amount of 140 tables in such a "BibleVersions.db" is not
uncommon and can be managed by SQLite in a good performance.
I'm not sure that form of division would be good. One basic rule of
database normalization is that you don't break-up data based on the
value of one of the fields because you can't make the table to lookup
data from be taken from a field you get in a query.
It is far less of a worse design than having an AFTER DELETE trigger
doing trivial processing when you expect to delete 30K records at a time
due to a "well-designed" 1NF schema.
Firstly, good design guides are only guides.
Secondly, having said that, either embrace the full idealism or none of
it, but doing some things the good way and others not, hampers the
"goodness" of the overall system.
Lastly, we usually use ON DELETE CASCADE Foreign Key relations to do
what you are doing with the trigger, but of course you are using FTS5
and I do not know if it can be done the FK way, or indeed how much the
FK way will be better considering the same Indexes remain.
Note - I don't have a good solution here, if it was me I would
immediately kill those triggers, see if it can be done smarter (or is
needed at all), or even if it can be done in code - OR - get rid of the
1NF schema and go with the suggested Bible-per-Table method and keep the
triggers (This will help nothing if the deletes are going to require
deleting 1 record (and its trigger) each through 30K tables. It's only
useful if it brings down the delete count).
If you cannot live without the triggers, nor can bring down the
delete-count, maybe do it in code, or one quick way I can think of is
using the ON DELETE to add those IDs to a "to_be_deleted" table and
simply delete everything fropm the second table with "WHERE ID IN
(SELECT ID FROM to_be_deleted)" before comitting the transaction,
perhaps combined with dropping and re-making the Indexes - but of course
this needs testing and might not be suitable for reasons I am unaware of
in your use case.
Either way, you are probably in for some lengthy processing.
Best of luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
