On Sat, Dec 6, 2014 at 2:10 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
> > You probably want your "name" fields in each table to be declared name > TEXT COLLATE NOCASE UNIQUE > Probably, but my statements were pseudo-, to illustrate the model I'm proposing. I didn't even bother to try to execute them. > Your ImageTags should also have an index UNIQUE (TagID, ImageID) > Why? That's the primary key reversed. Primary keys are unique by definition, aren't they? As for "access path" by tagid first, doesn't the skip-scan optimization (http://www.sqlite.org/optoverview.html#skipscan) take care of this? > The AUTOINCREMENT keyword in each of the Images and Tags table is > unnecessary. > Probably, although it should be noted that it has a subtle difference from INTEGER NOT NULL PRIMARY KEY in that it is guaranteed to never reuse values from deleted records (https://www.sqlite.org/autoinc.html). > You also want fields containing the same thing to have the same name > (unless you love typing). > You are probably referring to the ON clause. Actually I love explicitness and I tend to name fields according to their function, not the data they contain. IDs are IDs, names are names etc. in all tables. I guess it's a matter of personal taste but it can come handy when you are programming in an object-oriented language and can have a hierarchy of "entities" and their fields. You can create abstract ancestors that have these fields. Of course you can always use aliases. > Finding Images with various AND (intersect) and OR (UNION) of Tags is very > efficient. > > select * > from Images > where ImageId in (select ImageId from ImageTags join Tags on (TagId) > where name='summer' > intersect > select ImageId from ImageTags join Tags on (TagId) > where name='house'); > I assume the query planner will generate identical (optimal) plans for in-based queries and their semantically equivalent join-based ones where UNION becomes OR, INTERSECT becomes AND and MINUS becomes AND NOT. If this is true it is again a matter of personal taste. If this is not I would like you to elaborate please. Regards. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
