Search-as-you-type on a relational database

A search system, separate from a relational database, generates an index of information in the relational database that can be used to look up business records (or entities). A search system, that is also separate from the relational database, receives typing or other character inputs in a search user input mechanism and generates queries against the index based on the typing inputs, or other character inputs, received. The search system returns results and modifies those results as additional typing inputs, or characters, are received.

BACKGROUND

There are a wide variety of different types of search engines and search technologies used in current computer systems. Some such engines or technologies include web search engines, and search functions implemented in other applications, such as operating systems, web browsers, and data collaboration applications (such as note taking applications).

Some of these search engines and technologies implement a “search-as-you-type” function. In this function, as an end user is typing characters into a search field, the search engine performs repeated searches against a data store or an index. In one system, a search is executed each time the user pauses in their input for a given time (such as approximately 300 ms or a different time) to reduce or control the number of queries. In another system, one search (or query) is executed for each key stroke or character input by the user. In locating matching results, the search engine may perform prefix searching, in which the search engine estimates the entire query (based on the characters received thus far) and launches the estimated query. In another embodiment, the search engine searches for results that match the characters that have been input so far, and returns those matching results. As the user continues to type additional characters into the search field, the search engine refines the search based on the additional characters and returns results based on the refined search. It can be seen that the search-as-you-type function generates a large number of queries against the data store or data corpus that is to be searched.

There are a wide variety of different types of business data systems currently being used. Such systems include customer relations management (CRM) systems, enterprise resource planning (ERP) systems, line-of-business (LOB) applications, etc. Many of these business data systems are built on a relational database. Normally, in order to look up information in a relational database, a user generates a database query that is launched against an intra-database index. This type of database index must normally comply with all relational database properties (such as atomicity, consistency, isolation, and durability). Therefore, generating an intra-database index in a relational database can be time consuming and costly in terms of performance overhead.

In addition, many business data systems are accessed by a large number of users at a given time. Therefore, it can be difficult to service all of the various queries launched by the users against the relational database in a timely manner. Significantly increasing the number of queries launched against the relational database in a business data system would likely adversely affect performance.

SUMMARY

A search system, separate from a relational database, generates an index of information in the relational database that can be used to look up business records (or entities). A search system, that is also separate from the relational database, receives typing or other character inputs in a search user input mechanism and generates queries against the index based on the typing inputs, or other character inputs, received. The search system returns results and modifies those results as additional typing inputs, or characters, are received.

DETAILED DESCRIPTION

FIG. 1is a block diagram of one illustrative embodiment of a business data system98with a search-as-you-type system (hereinafter search system)100in communication with relational database system102.FIG. 1also shows indexing component104and client106that has a user interface component109that generates user interface displays108for user110. In one embodiment, client106is illustratively a browser or another application or component on a client device.

Business data system98can be any of a wide variety of different types of business data systems. For instance, it can be an enterprise resource planning (ERP) system, a customer relations management (CRM) system, a line-of-business (LOB) system or a wide variety of other business systems. In any case, the relational database system102is provided in business data system98for accessing business data records118. Business data component103illustratively implements the particular system (such as the ERP system, the CRM system, the LOB system, etc.) and performs the functionality that allows user110to access and manipulate the relational database system.

Processor129is also illustratively a computer processor with associated memory and timing circuitry (not separately shown). It is illustratively a functional part of business data system98and is activated by, and facilitates the functionality of, the various components and systems within business data system98.

FIG. 1shows that relational database system102includes a database server112, processor114and relational database116which, itself, includes the business data records118. It will be noted that while the present system can be described with respect to any type of data stored in a relational database, it is described herein with respect to a business database system that includes business data records118, for the sake of example. Business data records118can be a wide variety of different types of business data records, such as product records, customer records, contact records, financial records, sales records, personnel records, or a wide variety of other business records.

In the example, business data records118include entities120, look up metadata122(that is, metadata that allows individual entities120to be looked up or retrieved through searching), relations metadata124that expresses the relations between the various entities120, and a host of other metadata126.

Processor114is illustratively a computer processor with associated memory and timing circuitry (not separately shown). Processor114is illustratively activated by, and facilitates the functionality of, database server component112and other items in relational database system102. It will be appreciated, of course, that processor114can in fact include multiple processors or a single processor, and it can be integrated into database server component112or other items as well. It is shown separately as processor114for the sake of example only.

During normal operation of the business data system, a user110of relational database system102illustratively inputs queries against relational database116to look up business data records118. The queries are input, illustratively through user input mechanisms on UI displays108and provided to database server component112which executes the queries against the relational database and returns results based on the queries.

System98also includes search system100which illustratively includes processor128, search component130and search-as-you-type index (hereinafter search index)132. In operation, search system100implements search-as-you-type functionality against relational database116, without significantly increasing the indexing overhead or query overhead for relational database system102. This is described in greater detail below with respect toFIG. 3. However, for the sake of example, a brief overview is now provided.

Indexing component104illustratively intermittently indexes the business data records118in relational database116. Indexing component104does this to generate search index132. As user110is inputting a search (such as typing characters in a search field generated on one of UI displays108or another suitable user input mechanism) client106provides the inputs134to search component130which executes queries against search index132based on each individual character received. In one embodiment, one query can be executed for every character input by the user. This is but one example and the system is described this way herein. In another embodiment, the number of queries can be reduced by waiting to perform them until the user pauses in providing the inputs for a predefined time period. For instance, if the user pauses in typing for 300 ms, for example, a query is executed. Of course, the predefined time period can be shorter or longer, and can be determined empirically or otherwise. In any case, component130returns search results136based upon the queries executed against index132. As user110continues to type characters into the search field, search component130refines the search against index132based on those additional inputs and returns revised results136to client106. Client106illustratively displays the results136in close proximity to the search field on the UI display108that user110is using to conduct the search.

Before describing the search-as-you-type functionality in greater detail, one embodiment of the operation of indexing component104will be described with respect toFIG. 2.FIG. 2is a flow diagram illustrating one embodiment of the operation of indexing component104in generating search index132for search system100.

Indexing component104first determines whether it is time to index (or update the index for) business data records118in relational database116. This is indicated by block140inFIG. 2. Indexing component104can do this by intermittently or periodically indexing business data records118. The period or intermittent time between indexing operations can illustratively be predefined or defined by an administrator or user, as desired. The time between indexing operations can illustratively vary based upon application and based upon the frequency with which the indexed data is modified in relational database116. Frequently modified data can be indexed more often, while less frequently modified data can be indexed less often, or all data can be indexed at the same rate. In one embodiment, for instance, indexing component104updates index132every two days. This is an example only and other time periods can be used as well.

In any case, when indexing component104determines that it is time to index data, it accesses relational database116. This is indicated by block142inFIG. 2. Indexing component104then either creates or updates search index132based upon the information in relational database116. Creating and updating the search index is indicated by block144inFIG. 2. In doing so, indexing component104can index a variety of different kinds of information. In one embodiment, indexing component104indexes fields in business data records118that are selected by the user. Of course, the indexed fields can be predefined as well. Indexing the fields is indicated by block146.

Indexing component104also illustratively indexes the business data based upon look-up metadata122. That is, the individual entities120in relational database116illustratively include metadata that are used to locate the corresponding entity120in relational database116. For every entity120, there is illustratively a set of attributes that is marked as metadata122(e.g., as being needed to perform a look-up operation to locate the corresponding entity120). In one embodiment, indexing component104copies this look-up metadata122out of relational database116and places it in search index132. Indexing based on the look-up metadata is indicated by block148inFIG. 2. In addition, indexing component104can generate or update index132based on other information as well, and those fields can include name fields, description fields, note fields, customer account fields, menu items, labels for the menu items, etc. This other information is indicated by block150inFIG. 2.

In one embodiment, it will also be noted that the fields that are to be used by index component104in generating search index132may be of different types. For instance, in one embodiment, the fields that are indexed are only search fields. That is, they are used only to perform the searching in implementing the search-as-you-type functionality of search component130. Other fields may be used to perform that type of search functionality, but may also be marked as display fields which are displayed when the results136are returned to user110. Other fields may be displayed, but not used for searching, in one embodiment. Therefore, in one embodiment, the fields that are indexed can be marked as fields that are used only for searching, only for display, or for a combination of searching and display. This is indicated by block152inFIG. 2.

FIG. 2Ashows one embodiment of an illustrative index132which is generated by indexing component104. It can be seen that the index has a location ID field, a description field, an IS postal address field and a parent location field. Table153further assumes that indexing component104is indexing a store location ID field, but this field is not searchable (it is marked as being for display only). Assume that the index also stores the record ID (not shown inFIG. 2A). Further the “description” field is indexed for purposes of searching and display. In that case, the index will include the record ID, the location ID (which is marked for display only, and not for searching) and the description field (which is marked for both display and searching).

Once search index132is created, it can be used by search component130to perform the search-as-you-type function.FIG. 3is a flow diagram illustrating one embodiment of the operation of search system100in performing this. Search component130illustratively generates a user interface display which is provided by client106as one of UI displays108to user110. The user interface display is a search user interface that allows user110to input characters (such as through typing or otherwise) to search for entities in relational database116. Displaying the search UI to receive inputs to search relational data is indicated by block160inFIG. 3.

Search component130then receives the typing (or other character) inputs entered through the search UI. This is indicated by block162inFIG. 3.

Search component130then searches index132for entities represented in the search index132based on the typing inputs. This is indicated by block164inFIG. 3. Of course, search component130can perform a wide variety of different searches, using a wide variety of different search techniques. Some of those are shown inFIG. 3by way of example only. For instance, component130can perform phrase prefix searching166, simple prefix searching168, or other types of searching170.

Based on the query, component130returns results136to user110, through client106. In returning results, component130illustratively implements any role-based access or role-based security (or other security) that might be used by business data system98. This is indicated by block172inFIG. 3. Similarly, search component130can consider previous queries submitted by user110. For instance, if a user110has accessed a given entity previously, then the prefix search (or other search) being conducted can preferentially choose that entity over others. Considering previous queries in other ways is also contemplated. This is indicated by block174inFIG. 3.

In any case, once search component130launches the query against index132, it obtains search results136, and returns results136through client106and user interface displays108to user110. This is indicated by block176inFIG. 3. It will be noted that, in one embodiment, the UI displays108can be generated by user interface component109in a variety of different ways. The results can be displayed on a user interface display that displays all results that match the query. This is indicated by block178. Of course, the user interface display can display a subset of the results as indicated by block180and a “view all” option can be provided to user110so that the user can provide a suitable user input to view all of the results as well. The view all option is indicated by block182inFIG. 3.

FIG. 3Ashows one exemplary search user interface display164that can be used. User interface display164includes search field166and dropdown menu168that displays results136. It can be seen inFIG. 3Athat the user has typed the letter J in search field166. In response, search component130searches index132, and using the example index shown inFIG. 2Adescribed above, finds two entries. The first entry is JJ Field Trip Service Ltd., and the second entry is JJ Pollen Counting Investments Limited.

FIG. 3Ashows that when search component130is performing simple prefix searching, it searches the word in fields of the index that are to be searched (in this case the, “description” field). Since the user has typed “j” into search box166, the two results in results menu168are returned because they have a “j” at the beginning of one of the words or text segments in the description field. In one embodiment, this can be done as shown, or by using syntax, such as “j*”. This is exemplary only. In another embodiment, search component130locates any records that contain the letter “j” anywhere in the string.

FIG. 3Bis another embodiment of a search user interface display170. Search user interface display170is similar to search user interface display164except that the user has typed the letter “1” into search box166. Again, the results returned are the same as those shown inFIG. 3A, because search component130has found the letter “l” at the beginning of one of the words or text segments in the same two entries in the index.

FIG. 3Cshows another embodiment of a user interface display184. User interface display184shows an example where search component130performs a prefix search on phrases. It can be seen that the user has entered the character string “jj f” in search box166. Because search component130performs a prefix search on phrases in the index records, it returns only the record for “JJ Field Trip Service Ltd.” in dropdown display168. This type of searching assists user110in narrowing down results, from the simple prefix search described above with respect toFIGS. 3A and 3B.

It will also be noted that, if multiple fields in search index132are searchable by search component130, then text input by the user is searched across all searchable fields in index132. In one embodiment, this is done automatically, without requiring user110to input the particular fields that are to be searched. They are marked as searchable fields in the index and they are automatically searched by search component130in generating results based on the user inputs.

FIG. 3Dshows another embodiment of an exemplary set of index entries133. It can be seen that the index component104has indexed six fields including a country or region ID, a zip code, a state, a county, a city and a street. As the user types (or otherwise inputs) characters134, search component130searches all of the fields in the index entries133shown inFIG. 3Dand returns results. Table 1 below lists a set of inputs, all of which will match the first row shown inFIG. 3D. Table 1 also shows which columns will be matched.

TABLE 11. fry (match on STREET column)2. ved (match on CITY column)3. ad (match on STREET column)4. all (match on STREET column)

Table 2 below shows a list of inputs, all of which will match the second row shown inFIG. 3D. Table 2 also shows the particular column that will be matched.

TABLE 21. Du (match on ZIPCODE and CITY columns)2. FO (match on STREET column)

Of course, these are exemplary only and they provide examples of character inputs that will match the rows, because multiple different fields are searched by search component130in performing the search-as-you-type function.

In one embodiment, user110can also perform further structured searches against the returned search results136. This is illustrated by block184inFIG. 3. It will be noted that block184is shown in phantom, because it is optional.FIG. 3Eshows one illustrative user interface display186that allows the user to do this. In the embodiment illustrated, results136are returned and displayed in results pane188. The user can illustratively select one of the displayed results simply by clicking on it, or otherwise actuating it. Alternatively, the user can perform additional searching. For instance, in the embodiment shown inFIG. 3E, the user has indicated that the results are to be further filtered based on the “alias” field, because this is entered into filter field190. The user has also indicated that the results are to be filtered so that the alias field does not contain the letter string “amitkul” as shown in field192, but that it does contain the letter string “ab” as indicated in block194. After having entered these further search parameters, the user can simply actuate the “ok” button196and the results displayed in results pane188are further filtered or sorted by search component130, based upon the further inputs provided by user110in fields190,192and194.

Once search results136have been displayed on UI displays108to user110, the user can illustratively select one of the search results from the displayed set of results, or the user can provide additional typing (or other character) inputs so that search component130can continue to perform the search-as-you-type function and further narrow the returned results136displayed to user110. Determining whether the user has selected one of the results is indicated by block200inFIG. 3. Updating the search results based on further typing inputs is indicated by block202, and processing then reverts to block176where the updated search results are displayed to the user.

However, if, at block200it is determined that the user has selected one of the search results, then search component130returns and displays the record corresponding to the selected search result. This is indicated by block204. In one embodiment, when the user selects one of the search results, the selected result corresponds to one of entities120from business data records118. Therefore, search component130returns the selected entity and displays it to the user.

Business data component103also illustratively generates the proper relation between the selected entity and a target entity in relational database116. By way of example, assume that the user has created a project and needs to assign the project to a target entity. The user uses search system100to identify the target entity (such as an entity corresponding to a given customer). When the user selects the search result corresponding to the given customer from the returned results136, business data component103illustratively assigns the appropriate relation between the project just created and the given customer entity in relational database116. This may include, by way of example, defining an association between the entity corresponding to the given customer and the entity representing the project. Of course, other relations can be created as well between items input by user110and a target record in relational database116. Creating a desired relation between a selected entity and the target entity is indicated by block206inFIG. 3.

It can thus be seen that system100can be used in a wide variety of different contexts. One exemplary context is when user110is searching for a known value. Assume, for instance, that user110has a large quantity of paper orders to enter into business data system98. Each paper order contains the name of the customer for which the order is to be entered. User110opens a sales order form in business data system98. One piece of information to enter is the name of the customer that relates to the sales order. User110can simply type the first few characters (or even a single character) of the name of the customer from the paper sales order into a customer field on the sales order form and be presented with a dropdown menu (or other user interface display108) of returned results136, based upon the first few (or single) characters entered into system100. Search component130identifies the relevant fields that are to be displayed to user110as results136, and results136can be displayed so that the first result in the list is selected by default. Of course, if user110changes the focus in the list, then other results can be selected as well. As the user continues to type the characters without selecting one of the results, search component130launches additional searches against index132and updates the displayed results136.

In another example, user110can use system100to search for partially known values. Assume, for instance, that user110was interrupted in the task of entering sales orders into business data system98, but decides to resume that task. Assume further that user110was on the phone, earlier in the day, with a sales associate who gave user110details of a sales order to enter into system98. It may happen that user110has written down the details of the sales order on a piece of paper, but is unable to completely recall whether the name of the customer (that may be scribbled in a messy way on a piece of paper) was “Sunflaw Wholesales” or Sunglyph Wholesales” in system98. User110thus opens the sales order form in system98and navigates to a customer field and begins typing. After every character typed (or after a pause in typing by the user), user110is presented with results of customers that match the characters entered, thus far, in the query. Search component130can perform the search across multiple different fields such as customer name, description, notes, customer account, etc. As user110begins typing “su” there may be many customers that match the query. A subset of these can be presented to user110as part of user interface displays108, and user110can be provided an option to see all of the results, by actuating a suitable user input mechanisms. Now, user110decides to narrow results by typing an additional letter, and enters the letter “n” so that the entered string is “sun”. This may narrow down the results sufficiently that user110can now decipher the hand written notes taken earlier in the day.

System100can also be used to look up unknown values. Assume that, in another example, user110is again entering sales orders into system98and notices that one particular order has contact information mentioned. User110can navigate to the “contact” box within that entity and request a list of contacts. User110then notices that the contact list contains hundreds of contacts that have previously been entered by various sales associates into system98. However, user110can enter a region and country into one or more search fields. As user110begins entering characters for the region and country, search system130beings to narrow the list of contacts to those corresponding to the region and country character strings entered in the appropriate user input fields.

The embodiment shown inFIG. 4, specifically shows that business system98is located in cloud502(which can be public, private, or a combination where portions are public while others are private). Therefore, user110uses a user device504(which can be a client device with client106) to access those systems through cloud502.

FIG. 4also depicts another embodiment of a cloud architecture.FIG. 4shows that it is also contemplated that some elements of business system98are disposed in cloud502while others are not. By way of example, database system102can be disposed outside of cloud502, and accessed through cloud502. In another embodiment, some or all of the components of system98are also outside of cloud502. Regardless of where they are located, they can be accessed directly by device504, through a network (either a wide area network or a local area network), they can be hosted at a remote site by a service, or they can be provided as a service through a cloud or accessed by a connection service that resides in the cloud.FIG. 4further shows that some or all of the portions of system98can be located on device504. All of these architectures are contemplated herein.

FIG. 5is a simplified block diagram of one illustrative embodiment of a handheld or mobile computing device that can be used as a user's or client's hand held device16, in which the present system (or parts of it) can be deployed.FIGS. 6-9are examples of handheld or mobile devices.

FIG. 5provides a general block diagram of the components of a client device16that can run components of system98or that interacts with system98, or both. In the device16, a communications link13is provided that allows the handheld device to communicate with other computing devices and under some embodiments provides a channel for receiving information automatically, such as by scanning. Examples of communications link13include an infrared port, a serial/USB port, a cable network port such as an Ethernet port, and a wireless network port allowing communication though one or more communication protocols including General Packet Radio Service (GPRS), LTE, HSPA, HSPA+ and other 3G and 4G radio protocols, 1Xrtt, and Short Message Service, which are wireless services used to provide cellular access to a network, as well as 802.11 and 802.11b (Wi-Fi) protocols, and Bluetooth protocol, which provide local wireless connections to networks.

Under other embodiments, applications or systems (like system98) are received on a removable Secure Digital (SD) card that is connected to a SD card interface15. SD card interface15and communication links13communicate with a processor17(which can also embody processors114,128or129fromFIG. 1) along a bus19that is also connected to memory21and input/output (I/O) components23, as well as clock25and location system27.

FIGS. 6 and 7show one embodiment in which device16is a tablet computer600. InFIG. 6, computer600is shown with user interface display184fromFIG. 3Cdisplayed on display screen602.FIG. 7shows computer600with user interface display186fromFIG. 3Ddisplayed on screen602. Screen602can be a touch screen (so touch gestures from a user's finger604can be used to interact with the application) or a pen-enabled interface that receives inputs from a pen or stylus. It can also use an on-screen virtual keyboard. Of course, it might also be attached to a keyboard or other user input device through a suitable attachment mechanism, such as a wireless link or USB port, for instance. Computer600can also illustratively receive voice inputs as well.

FIGS. 8 and 9provide additional examples of devices16that can be used, although others can be used as well. InFIG. 8, a smart phone or mobile phone45is provided as the device16. Phone45includes a set of keypads47for dialing phone numbers, a display49capable of displaying images including application images, icons, web pages, photographs, and video, and control buttons51for selecting items shown on the display. The phone includes an antenna53for receiving cellular phone signals such as General Packet Radio Service (GPRS) and 1Xrtt, and Short Message Service (SMS) signals. In some embodiments, phone45also includes a Secure Digital (SD) card slot55that accepts a SD card57.

The mobile device ofFIG. 9is a personal digital assistant (PDA)59or a multimedia player or a tablet computing device, etc. (hereinafter referred to as PDA59). PDA59includes an inductive screen61that senses the position of a stylus63(or other pointers, such as a user's finger) when the stylus is positioned over the screen. This allows the user to select, highlight, and move items on the screen as well as draw and write. PDA59also includes a number of user input keys or buttons (such as button65) which allow the user to scroll through menu options or other display options which are displayed on display61, and allow the user to change applications or select user input functions, without contacting display61. Although not shown, PDA59can include an internal antenna and an infrared transmitter/receiver that allow for wireless communication with other computers as well as connection ports that allow for hardware connections to other computing devices. Such hardware connections are typically made through a cradle that connects to the other computer through a serial or USB port. As such, these connections are non-network connections. In one embodiment, mobile device59also includes a SD card slot67that accepts a SD card69.

Note that other forms of the devices16are possible.