Hello: I have about 3000 electronic images that I am preparing to distribute to my family members. They use a variety of operating systems, so I am providing the images on remote hard drives, with the images divided into folders based on years the images were created. All images were obtained via scanning of negatives and slides, and each image has been keyworded in EXIF format.
What I want is to create a master SQLite database catalog of all the images, and I have come up with a proposed schema: TABLE main (all pertinent image data such as date, location in folder, etc.) TABLE core_keywords (id, name) --This table would only hold the names of my immediate family members, each with a corresponding id TABLE other_keyword (id,name) -- all other keywords I have thought to create two keyword fields in the main table, one to hold the ids of the core_keywords (comma separated) and one to hold the ids of the other_keywords, also comma separated. What I cannot devise is an elegant method to SELECT based on the core_keywords to achieve the following sorts: 1- Find images with a single core_keyword id, that is, only images of a single person, no other core persons in the image 2- Find images with a specific set of core_keyword ids, such as 1 and 6 or 2 and 5 and 7, etc., with no other core persons in the image The idea is to create a document with lists of all images that are exclusive to single individuals, specific pairs, etc., so that family members can easily find themselves or groups, regardless of image catalog software they use on their particular systems, which may or may not be able to perform these types of sorts. I am not asking anyone to actually write the SELECT statements for me, but rather point me toward the operands that would achieve my goal. I have read through the documentation, and I cannot seem to generate the logic in my head to SELECT WHERE core_id is only 4. If anyone has an idea on a more efficient database design, or TABLE schema, please do not hesitate to proffer your thoughts. I am hoping to have it all figured out BEFORE I load up the tables with data. (I am actually still scanning images at this stage, but trying to prepare for the next phase.) Thank you very much for your time and consideration. Craig Smith cr...@macscripter.net _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
