> BareFeetWare-2 wrote: >> >> In that case, you should cache the counts in a separate table or two. That >> has a negligible overhead when you add a movie (which is infrequent), and >> basically no overhead when viewing (which is frequent). > I am doing that but in the application level, the down side is that I am > updating the cache on any change, I check the modified time of the database.
I strongly suggest doing as much as you can at the SQL level, especially where it concerns the integrity of your data. You should find it faster, more reliable, self contained and logical. > BareFeetWare-2 wrote: >> >> Do that with triggers (which are a good thing in this context), to change >> the relevant count when an insert, delete or update occurs in the "Movie >> People" table. Something like: >> > I think, not sure, that there will be a problem with that code. > I am not an expert so I had to stare that code some time to understand it. > If I am correct you are adding and subtracting 1 on every insert or delete. Yes, and doing a delete/insert combo for an update (ie if a person's capacity in a movie changes). > The problem is that an insert may not be unique so before augmenting the > counter you have to check if it is unique and therefore you would have to > run a count(distinct col) on every insert which would be overkill. If I understand you correctly, the non-unique criteria is already handled by the schema design. The "Movie People" table allows the same person to be listed as the director for multiple movies. The constraints on the "Movie People" only require no nulls and unique combinations of Movie, Capacity, People (ie the same person can't be listed as director for the same movie twice, but can be listed as director for two movies). Because it uses the "unique" constraint for this, SQLite automatically builds an index "Movie People"(Movie_ID, Capacity_ID, People_ID), so that whenever you try to insert a new row, it quickly checks if it already exists. You can throw new non-unique rows at it with "insert or ignore" if you just want it to ignore duplicates, which then won't trigger the count increase because nothing was inserted. Or use plain old "insert" if you want to be alerted to any attempted unique violations. Does tis answer your needs? If not, please explain further, but it will probably only require modifying a constraint in the schema I proposed, rather than denormalizing or partitioning. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
