CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2019
    Location
    Java 1.8
    Posts
    12

    How would I begin building a Search Engine for a relational database?

    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: 382
Size:  25.2 KB

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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: How would I begin building a Search Engine for a relational database?

    Not a SQLLite or Java expert. Depending on the what columns are included in the query, you can dyamically form a query. This would be trivial to implement but to get any kind of performance the columns that you are searching on will need to be indexed (which may negatively impact insert performance).

    Another option is to create a de-normalized table or tables that flattens out the data and is only used for searching. You would have to come up with a synchronization strategy to keep the normalized source data in sync with the flattened table(s) and of course have indexes on the columns. This would be essentially a read only table used f9r searching.

  3. #3
    Join Date
    Jan 2019
    Location
    Java 1.8
    Posts
    12

    Re: How would I begin building a Search Engine for a relational database?

    Hi Arjay!
    Thanks for the thoughts. I'm not an expert either with SQLite or or Java, but know a enough to be dangerous. This is my first experience with SQLite. They have a feature called FTS5 which is a Full Text Search that works in conjunction with indexing but appears a little more robust. I'm trying to see if there is more I can learn about it on the SQLite Tutorial site.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured