I apologize, I sent this from a different email than I registered accidentally. Hi Everyone! The Ask and what I’ve done: I'm building a research database with an embedded SQLite DB using Intellij for development. I've created the data entry point for the users; however, I'm looking for anything anyone might want to share or suggest as a code, link, or design, whatever it may be, on developing a search engine for a relational database. I’ve been reading through tutorials on SQLiteTutorials.net to learn a little more about indexing and searching. I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having difficulty finding anything that may assist with a multi-table search. I guess it would nice to have an example to follow. I wouldn’t think this would be the first-time for this, but I’m a little naïve too. I'm not asking for someone to solve the problem but just provide a direction that may save a lot of research time. I don’t know if my images will post or be available through this email, but I've provided some images below of the GUI and the SQLite DB which has 8 tables all linking back to the primary table called "Source". This is a new task for me and my development skills in Java.
The Goal: This Research DB users want to be able to search the database either by Source, Topic, Question, Comments, Quotes, Authors, which most are a one to many with one a many to many relationship. A single source, for example a book entered in the database, could be linked to multiple topics, questions, Comments, Quotes, etc. So the users may want to search each of these separately on a particular subject, or possibly all the tables for a particular subject. Topic will help the mostly, but a topic may be addressed in a comment or quote, etc, that may not be associated directly with a particular topic. So there is a need to search a number of fields over 8 tables. My thoughts are that the search criteria should probably return into a table list, then when the user selects a particular row, this data would populate into corresponding fields for readability similar to the "Entry" tab (see below). The only thing I suspect may make this easier than expected is that no matter whether the user searches by Topic, or Question, or Comment, etc, the search will always join all the tables and return all the fields for anything linked back to a source. However, the source will be associated to many of each of those. Thanks, Scott _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
