We do a program for cataloging images by assigning keywords (tags) to them.
An image has the following keywords "John", "Jane", "Portrait".
Another image has the following keywords "John", "Jane", "Wedding".
When the user tries to catalog the next image and adds the "John" keyword, we want to appear in the 'Suggested' area the keyword "Jane".
How can we extract (using SQL) the suggested keywords based on the most possible combinations?
Note that Images can have more keywords assigned and in 'Suggested' area is best to have more suggestions.
The relevant table is called
TagsTree and it has the following creation DDL:
CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))
...where ImageID is FK to the photo entity and TagID is FK reference to the keyword entity.
Also, any other hints regarding schema etc. are greatly appreciated.
PS: The DB engine is SQLite3, if it matters.