On 30/06/2011, at 8:43 PM, Ian Hardingham wrote: > Hey Tom, many thanks for the help.
You're welcome. > At times I will need to identify whether a match is a "tournament match" > or not. It seems from what you're suggesting that I should do a select > on the tournamentMembershipTable (with zero results being "no") rather > than having a tournamentMatch boolean in the matchTable. Good question. You don't need to, and shouldn't count results and then check if it is zero. It's commonly done but it's bad practice and unnecessary. Logically, all you need to ask is if just one match exists or to ask if it appears anywhere in the list, without needing to scan the entire list. Fortunately SQL has two methods to do just that. select exists (select 1 from tournamentMembershipTable where matchID = theDesiredMatch); or: select theDesiredMatch in (select matchID from tournamentMembershipTable); If your condition requires checking more than one column (which is not the case here), then the exists method is the only option. Note that since matchID is the primary key, it's already effectively indexed. > This seems mildly counter-intuitive to me but I'm trying to learn DB design! Since only some matches will be part of a tournament, it's bad practice (not normalised) to create a foreign key reference column in matches to point to tournament, since it will mostly just have a null value. It's better to have a separate table, like the one I gave: >> create table tournamentMembershipTable >> ( matchID integer primary key not null references matchTable (id) on >> delete cascade >> , tournamentID integer not null references tournament (id) on delete >> cascade >> ) Which only needs a row for each actual relation (ie each match that is part of a tournament). The "delete cascade" will keep it in sync so that if you delete a match or tournament, the related rows in this table will delete also. Since each match can appear only once in this table, I've set it up to use the table's own primary key column (which is an alias of the always created rowid primary key column) so it doesn't waste overhead with another column it doesn't need. Remember to turn on: pragma foreign_keys = YES; whenever you open a connection to the database. Unfortunately it's off by default. Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- 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
