Hi Everyone!

The Ask:
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'm not asking for someone to solve the problem but just provide a direction that may save a lot of research time. 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.

What I've done:
I've looked over some YouTube videos and read some articles, but most of them deal with searching a single table within a database and its fields, but not across multiple tables. So this may be a little more intuitive than usual and so I could use a little jump start from someone whom may have experienced a similar challenge.

The GUI demonstrates the information being entered and most likely will be similar to search tab. The SQLite shot just shows how many tables will be involved. The only many-to-many relationship is the Author_by_Source table, all others are one-to-many (i.e. Source to many (other tables)).

Name:  GUI.jpg
Views: 172
Size:  25.2 KB

Name:  SQLite.JPG
Views: 167
Size:  35.2 KB