Overview
In the previous post we discussed the custom requirements and procedure of designing a full text search on relational database in a business domain driven fashion. In this article I will share high level design diagram depicting all the components, their inter-connectivity and general flow application to database end. Also I will describe each component individually.
Design
Design has always been requirement oriented. Even for the same requirement there could be N design variation. Following is one possible design implementation for business domain driven full text search, to represent overall components. It is very high level and generic, to demonstrate involvement of different components and flow of information in between them.
Components Description:
DataTier / Server Code
Classes and functionality for full text search is represented as part of DataTier component. It could be a different components using DataTier. These parts are presumably written using application programming language. Its components are:
- Full Text Search Query<T>: Is a generic class representing search query, with keywords provided by user and type of search i.e. inclusive or exclusive, and provided type T would be used to identify type of search, and associated stored procedures.
- Search Query Transformer: Is a component made up of implementation for inclusive and exclusive transformers, That will take the keywords provided by user from Search Query object, and will transform it to search engine ready keywords, adding AND/OR operators where needed and handling double quotes as might be provided by user.
- Procedure Mapping Provider<T>: Is a generic class to map stored procedures with type T.
- Full Text Search Response<T>: This class represent search response, and will hold result generated from search.
Database
Main logic of text searching, indexing, query building and full text catalogue resides in database engines.
- Table A (Pivot Table): Is our main pivot table. So it represent T in type of search we are designing. For more information on Pivot table, refer to this article.
- Table B, C and N related tables: These are the tables, which are directly or indirectly related with Table A through physical (constraint) or logical relationship. These tables can include embedded documents.
- Index A, B, C: These are the Indexes maintained by Database engine Text Catalogue, when we call for rank against keyword in stored procedure from each index, it return matching rank, and ID of record with which rank is associated. We do need to workout on this part, as it is fully managed by database engine.
- Catalog managed FTS keywords: This is a system repository (normally table) in which system maintains list of all different keywords which it collects through indexing. This provides a real data keyword list for features like keyword suggestions.
- Suggest Keyword SP: Is a stored procedure, Which we use to provide keyword suggestions on the fly as the user type. It retrieved the keywords from catalog managed keywords source, by union of all keywords extracted from all the related tables.
- Full Text Search SP: This is the main SP for type T, that will execute the custom query we have written for search N tables, based on transformed keyword. Each full text query native command will return results and rank. We will multiply the rank with weightage we assigned to each object, and will then sort the results based on calculated rank.
Summary
In previous post, we went in to theoretical details of the concept and design process of business type driven full text search on relational database, why it is needed, and general concepts. In this post, we have discussed the design and the general components we need to implement to achieve the functionality.