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
