What's wrong with a properly normalised schema like this: create table main (id, name,...);
create table keyword (id, label); create table crossref (id_main, id_keyword); The concept of core and other keywords is a bit arbitrary. What is important (i.e. core) today might not be so tomorrow. Parsing comma separated lists in a single attribute is likely to be a bother. On Mon, 2009-06-01 at 21:21 -0700, Craig Smith wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
