Method and System for a De-Normalized Electronic-Catalog
A system (100) and method (700) is provided for searching content in a de-normalized database. The method can include receiving (702) a search request from a user, constructing (703) a configuration query from a configuration table based on an identity of the user, and executing (704) a single fetch into a content table using the configuration query to retrieve content from the content table. The method can further include generating one or more threads for the one or more configuration queries, executing the one or more threads in parallel, distributing and redirecting the executing across one or more applications clusters, and presenting one or more query results to a common results table
Latest Enporion, Inc. Patents:
The present invention relates to database management systems and, more particularly, to methods for searching database systems.
BACKGROUNDDatabase management systems are widely deployed for managing business to business e-commerce. As an example, supply chain management solutions in an e-commerce environment rely heavily on database management systems to give buyers and sellers access to online procurement transactions. Internet technology for supporting database management has also resulted in powerful and economical tools for supply chain professionals to improve supply chain performance. However, it is common for such database systems to employ computationally extensive searching methods. In particular, many database systems are based on relational database models that require access to numerous tables. The relational tables allow for powerful managing operations on vast quantities of data. However, the overhead involved with relational database management adversely affects search engine performance. The searches are generally conducted in accordance with the structure of the relational database system and result in decreased search performance.
As the number of buyers and sellers subscribing to an e-commerce system increases, the amount of data within the database increases. Consequently, a search for content within the databases can result in a longer wait time, thereby leading to customer dissatisfaction. A need therefore exists for increasing a search performance into a database while providing scalability.
SUMMARYEmbodiments of the invention are directed to a method and system for searching a database. The system can include a de-normalized database having one or more supplier content tables, a configuration table for dynamically managing a content of the one or more supplier content tables, and a search engine for conducting a search into one or more categories of a supplier content table and retrieving content in a single fetch. A supplier configuration query can identify one or more categories in a supplier content table that are searched by the search engine. A user can enter a search request and the configuration table can generate one or more supplier configuration queries based on a customer identity and a customer view. A configuration query specifies a single fetch into the one or more supplier content tables. The configuration table can track a supplier, a view, and one or more price structures for the one or more supplier content tables to configure the content at a customer node level. The system can further include a data loader and scrubber for populating the configuration table, and a threading module for executing one or more configuration queries in parallel. The system can further include a load balancer for distributing and redirecting the executing across one or more applications clusters.
Embodiments of the invention are also directed to a method for searching content in a de-normalized database. The de-normalized database can include one or more content tables. The method can include receiving a search request from a user, constructing a configuration query from a configuration table based on an identity of the user, and executing a single fetch into the de-normalized database using the configuration query. In one arrangement, the configuration query can identify a search for one or more pricing structures in a content table based on an identity of the user. The method can further include determining a user view associated with the search request, and including the user view with the configuration query for narrowing a search in the content table. In one arrangement, a supplier configuration table can be created by determining a number of suppliers and buyers, and generating one or more supplier configuration queries based on the number of suppliers for each buyer.
The method of searching can further include generating one or more threads for the one or more supplier configuration queries, executing the one or more threads in parallel to produce one or more query results, and presenting the one or more query results to a common results table. The one or more query results can be presented and sorted in a common format in the common results table. The results can be displayed to a user. A loading of the threads can be balanced over one or more servers to provide scalability for hosting multiple products. The one or more threads can be executed independently in the one or more servers. The method can further include clustering common search requests to provide scalability for supporting multiple users.
Embodiments of the invention are also directed to a method for creating a configuration table. The method can include identifying at least one supplier associated with one or more content files, generating a supplier content table from the one or more content files, identifying a buyer of the supplies listed in the one or more content files, generating a supplier configuration that matches a pricing structure of the buyer in the supplier content table based on an identity of the buyer, and loading the supplier configuration in the supplier configuration table.
The method can further include determining one or more suppliers available to the buyer, generating a supplier content table for each additional supplier, generating a supplier configuration for each additional supplier based on the buyer's access to one or more pricing structures in the supplier content table, and updating the configuration table by loading the supplier configuration for each additional supplier into the supplier configuration table. The supplier configuration identifies which structures in a supplier content table are available to search by a buyer. Structures can be dynamically added to the content table for expanding a supplier offering. The method can further include determining a view associated with the buyer, and entering the view in a view structure in the supplier content table. The view can identify one or more entries in one or more structures of the supplier content table that are available for search to the buyer.
In one arrangement, a supplier content table can be generated by denormalizing a relational database model of the one or more content files to generate a column structured table, wherein the denormalizing includes converting one or more relational tables in the relational database model into columns in the column structured table, wherein the one or more relational tables represent the one or more content files. As an example, a supplier configuration can be a column formatted table having a content column, a price column, or a view column, wherein the columns are the structures of the supplier content table.
The features of the system, which are believed to be novel, are set forth with particularity in the appended claims. The embodiments herein, can be understood by reference to the following description, taken in conjunction with the accompanying drawings, in the several figures of which like reference numerals identify like elements, and in which:
While the specification concludes with claims defining the features of the embodiments of the invention that are regarded as novel, it is believed that the method, system, and other embodiments will be better understood from a consideration of the following description in conjunction with the drawing figures, in which like reference numerals are carried forward.
As required, detailed embodiments of the present method and system are disclosed herein. However, it is to be understood that the disclosed embodiments are merely exemplary, which can be embodied in various forms. Therefore, specific structural and functional details disclosed herein are not to be interpreted as limiting, but merely as a basis for the claims and as a representative basis for teaching one skilled in the art to variously employ the embodiments of the present invention in virtually any appropriately detailed structure. Further, the terms and phrases used herein are not intended to be limiting but rather to provide an understandable description of the embodiment herein.
The terms “a” or “an,” as used herein, are defined as one or more than one. The term “plurality,” as used herein, is defined as two or more than two. The term “another,” as used herein, is defined as at least a second or more. The terms “including” and/or “having,” as used herein, are defined as comprising (i.e., open language). The term “coupled,” as used herein, is defined as connected, although not necessarily directly, and not necessarily mechanically. The term “processing” can be defined as any number of suitable processors, controllers, units, or the like that are capable of carrying out a pre-programmed or programmed set of instructions.
The terms “program,” “software application,” and the like as used herein, are defined as a sequence of instructions designed for execution on a computer system. A program, computer program, or software application may include a subroutine, a function, a procedure, an object method, an object implementation, an executable application, an applet, a servlet, a source code, an object code, a shared library/dynamic load library and/or other sequence of instructions designed for execution on a computer system.
Embodiments of the invention are directed to an electronic catalog (e-catalog) system for facilitating a rapid search into a de-normalized database. In particular, content can be searched with a single fetch. The e-catalog allows a large number of organizations and departments to provide custom and configurable searches into a large number of content databases. For example, the e-catalog system can be integrated within a multi-vendor environment for supporting an on-line buyer and supplier market. In one application, the e-catalog system allows buyers to conduct rapid searches into one or more supplier tables for identifying one or more supplies available to the buyer. A purchasing relationship between the buyer and the supplier can determine what supplies are available to the buyers and the pricing structures associated with the supplies. Pricing structures can be transparently included within a configuration table of the e-catalog to identify buyer pricing arrangements. Suppliers can also update the one or more supplier tables to include additional supplies and update the pricing structures.
The e-catalog system can provide high scalability in both the number of users and the number of products. The e-catalog can support content from various suppliers and buyers from multiple selling and purchasing organizations. In one arrangement, the e-catalog system can provide supply chain management services that increase the efficiency of business transactions. For example, the e-catalog system can assist buyers and suppliers in reducing lifecycle costs of goods and services through both price and process savings. The e-catalog system can provide underlying functionality for an on-line commerce environment for conducting buyer and supplier electronic transactions. As one example, on-line commerce environment can enable and support supplier connections and provide a full scope of supply chain support from sourcing to invoicing and payment.
Specifically, the e-catalog system can include a configuration table that manages a search into a de-normalized database. The configuration table provides custom configurations that allow a user to search content tables of one or more suppliers with a single fetch of a configuration query. The content tables are arranged in a structured format that allows for the generation of a single configuration query. As one example, a configuration query identifies which content tables can be searched based on an identity of the user. A configuration query can be generated from the configuration table and supplied to a search engine to search for content in one or more content tables in a single fetch. The content tables are highly de-normalized and are managed by the configuration table. Moreover, structures can be dynamically added to the content tables to expand content.
Referring to
The e-catalog system 100 can be deployed within a buyer and supplier e-commerce marketplace to facilitate buyer and supplier business-to-business transactions. The e-catalog system 100 can be deployed within an intranet, an extranet, the Internet, or any other suitable communication setting for providing connected or on-line e-commerce. As one example, the e-catalog system 100 can provide connection to one or more buyer and supplier networks for receiving purchase request information and product information. For example, the server 130 can establish data communication with one or more suppliers, 131 and 135, and one or more buyers, 101 and 102. The server 130 may host supplier information locally on the server 130, or through a remote connection to the supplier. In the foregoing description, buyers and suppliers will be referred to as entities which provide communication. For example, Buyer 1 can be laptop 101 and Supplier 1 can be server 131, but is not limited to this arrangement.
Briefly, the configuration table 124 specifies categories within the content tables 122 that are presented within a configuration query to the search engine 125. The search engine 125 can be a full-text search engine or a relational database search engine contained within the server 130. Alternatively, the search engine 125 may be on a remote server or in a distributed network. The search engine 125 can search data specific to categories of the content tables 122 for responding to a search request. In particular, the configuration table 124 can produce a configuration query in response to a buyer's search request based on configurations within the configuration table 124. A configuration can define an availability or access to products between one or more buyers and one or more suppliers. The configuration query can be used as a single fetch to the search engine 130 to narrow the field of search and reduce a number of search requests within the server 130. In one arrangement, the configuration table 124 can incorporate buyer and supplier relationship information for determining which products listed by one or more suppliers are available to a buyer.
In practice, it is common for suppliers to utilize relational databases. Relational databases and relational database searches are known in the art. A relational database is a database structured in accordance with a relational model. For example, Supplier 131 may have a relational database 141 containing one or more relational tables having a relation specified by an index table. For example, the index table 143 identifies a relationship of data between the price table 144 and the view table 145. The price table 144 may identify prices of one or more supplies, and the view table 145 may identify the prices a buyer sees for those supplies. Accordingly, extracting data from the relational database generally involves accessing more than one relational table. For example, in order to fulfill a search request on a specific product, Supplier 131 accesses the index table 143 to determine which price tables 144 and view tables 145 correspond to the product. In this case, the search takes 3 fetches into each table for responding to a search request.
The number of fetches into the relational database 141 can be reduced by de-normalizing the relational database. One aspect of the invention, is a method of de-normalizing a database. Data normalization is the process of defining common descriptions and attributes for the same or similar products, for example, those products offered by different suppliers. Within the context of a relational database model, normalization refers to the overhead associated with the relating of one or more relational tables. Accordingly, data de-normalization is the process of removing the overhead associated with relational models, and placing the data of the one or more related tables into a single table. The single table contains all the data from the one or more relational tables. However, a de-normalized data model requires data configuration management to manage content within the single table. The content refers to the data within the single table and can include product description information, view information, and pricing information.
In order to reduce the number of fetches performed by a supplier into a relational database, the server 130 de-normalizes the data to one or more supplier relational databases. The server 130 can store the de-normalized data in a supplier content tables 122 which can be accessed in response to a search request. The supplier content tables 122 can be stored at the server 130 or the Supplier server 131. The server 130 can de-normalize supplier relational databases for more than one Supplier to produce one or more content tables 122, herein called supplier content tables. In principle, a supplier content table 124 can be generated for each supplier. It should be noted that the supplier content tables 122 refers to a collection of supplier content tables and not just one table. It should also be noted, that data from a supplier can be entered in a de-normalized format. The de-normalization process of the server 130 is not restricted to de-normalizing only relational databases. Notably, a supplier content table 122 can be generated from tables other than relational tables.
Referring to
Referring to
A user input module 107 allows the buyer to enter in a search request for a product. In one arrangement, the user input module can be a web page or intranet site hosted by the server 130 (See
Referring, back to
Referring to
In the particular example of
A configuration identifies categories within the supplier content table that are specific to a particular buyer. Recall, each supplier can have a supplier content table. Each configuration in the configuration table identifies those categories that are specific to a buyer. In particular, the configuration defines which categories are available for search in a supplier content table. For example, configuration 411 (i.e. arrows in
Referring to
Understandably, the configuration table 124 keeps track of the purchasing relationships between one or more buyers and one or more suppliers. Referring back to
Referring to
A first part of the method 600 involves identifying suppliers. At step 602, at least one supplier associated with one or more content files can be identified, wherein the content files describe supplies provided by the at least one supplier. For example, referring to
A second part of the method 600 involves identifying buyers. At step 606, a buyer of the supplies listed in the one or more content files can be identified. For example, a buyer may register with the e-catalog system 100 on-line or through contracts. Alternatively, a supplier may provide registration information for one or more of their buyers. At step 608, a buyer's access to one or more pricing structures in the supplier content table can be determined. Pricing information, or access to products, is generally provided by the Supplier and is made available to the e-catalog system 100. For example, a supplier will provide pricing information and access information pertaining to one or more buyers. Given this information, at step 610, a supplier configuration can be generated for each buyer. The supplier configuration matches a pricing structure to the content in the supplier content table. That is, the pricing structure identifies products that are available to the buyer for search based on the buyer's access. The buyer's access can be determined by the buyer's identity. For example, as discussed in
The supplier configuration corresponds to the description of the configuration discussed in
Notably the steps of creating a supplier configuration include determining one or more suppliers available to the buyer, generating a supplier content table for each additional supplier, generating a supplier configuration for each additional supplier based on the buyer's access to one or more pricing structures in the supplier content table, and updating the configuration table by loading the supplier configuration for each additional supplier into the configuration table (124). The supplier content tables (122) can be dynamically expanded to include new product offerings. Accordingly, the configuration table (124) can keep track of the new product offerings as a result of the updated supplier content tables. For example, referring back to
The method 600 can further include determining a view associated with the buyer, and entering the view in a view structure in the supplier content table. The view (See
Notably, the method 600 for creating a configuration table is a first step for performing a search query. Upon creating the configuration table 124, a buyer can search into one or more supplier content tables based on one or more configurations in the configuration table 124. Consequently, the e-catalog system 100 provides a rapid search based on one or more configurations which are specific to a buyer.
Referring to
At step 702, a search request can be received from a user. For example, referring back to
At step 704, a supplier configuration query can be constructed from a configuration table based on an identity of the user and the search request. A supplier configuration query can be a select statement that is submitted to a full text search engine. For example, referring back to
An exemplary supplier configuration query 800 (e.g. select statement) is shown in
For illustration, two sets of supplier configurations are shown; one set for Buyer 1 (101), and one set for Buyer 2 (102). The supplier configurations correspond to the configurations shown for Buyer 1 and Buyer 2 in
SELECT [price 2] FROM [SUPPLIER 1] WHERE [view name] [token]
where “view name” may be a unique identifier based on an identify of the buyer, and token is the search word provided in a search request. For example, referring back to
SELECT [price 1,3,4] FROM [SUPPLIER 2] WHERE [view name] [token]
The second select statement (i.e. supplier configuration query) can be provided to the search engine 125 (See
Similarly, Buyer 2 (102) has two supplier configuration queries 821 and 822 corresponding to configurations 421 and 422 based on the relationships shown in layout 500 of
Referring to
Notably, the supplier content tables are arranged within the content tables 122 in a highly de-normalized structure. Accordingly, the threaded supplier configuration queries (901-902) are optimized for a single fetch based on the parameters passed to supplier configuration queries from the configuration table 124. The parameters identify the categories within a supplier table that can be searched. Notably, a supplier configuration query can be generated for each supplier query. Understandably, all threaded supplier configuration queries can be executed in parallel based on the computing architecture shown in
Moreover, the scalability of the e-catalog system 100 can be increased by balancing the search over multiple servers. Referring to
Referring to
Where applicable, the present embodiments of the invention can be realized in hardware, software or a combination of hardware and software. Any kind of computer system or other apparatus adapted for carrying out the methods described herein are suitable. A typical combination of hardware and software can be a mobile communications device with a computer program that, when being loaded and executed, can control the mobile communications device such that it carries out the methods described herein. Portions of the present method and system may also be embedded in a computer program product, which comprises all the features enabling the implementation of the methods described herein and which when loaded in a computer system, is able to carry out these methods.
While the preferred embodiments of the invention have been illustrated and described, it will be clear that the embodiments of the invention is not so limited. Numerous modifications, changes, variations, substitutions and equivalents will occur to those skilled in the art without departing from the spirit and scope of the present embodiments of the invention as defined by the appended claims.
Claims
1. A system for searching a database, comprising:
- at least one content table having a de-normalized structure, wherein data in the at least one content table is organized by category;
- a configuration table for dynamically managing content of the at least one content table and generating at least one configuration query into the at least one content table; and
- a search engine for conducting a search in at least one category of the at least one content table and retrieving data from the at least one category in a single fetch using the at least one configuration query,
- wherein the at least one configuration query identifies at least one category in the at least one content table that is searched by the search engine in one fetch.
2. The system of claim 1, further comprising:
- a customer input module for specifying a search request and determining a customer identify,
- wherein the configuration table tracks a supplier, a view, and at least one price structure for the one or more content tables to configure the content at a customer node level based on the customer identify.
3. The system of claim 1, further comprising:
- a data loader and scrubber for populating the configuration table; and
- a threading module for executing at least one configuration query in parallel.
4. The system of claim 3, further comprising:
- a load balancer for distributing and redirecting the executing across one or more application clusters.
5. A method for searching content in a de-normalized database, comprising:
- receiving a search request from a user;
- constructing a configuration query from a configuration table based on an identity of the user; and
- executing a single fetch into a content table using the configuration query to retrieve content from the content table,
- wherein the configuration query identifies a search for at least one pricing structure based on the identity of the user.
6. The method of claim 5, further comprising:
- determining a user view associated with the search request; and
- including the user view with the configuration query for narrowing a search in the supplier content table.
7. The method of claim 5, wherein the executing a single fetch into a content table is performed by a relational database search engine.
8. The method of claim 7, further comprising:
- assigning a unique identifier to the user view; and
- adding the unique identifier to a view structure in the content table,
- wherein the relational database search engine associates the unique identifier with a configuration for retrieving content from the one or more content tables.
9. The method of claim 8, further comprising:
- separating one or more unique identifiers by a space for stemming a data query in the one or more content tables.
10. The method of claim 5, further comprising:
- determining a number of buyers and suppliers; and
- generating one or more supplier configuration queries based on the number of suppliers available to the buyers.
11. The method of claim 5, further comprising:
- generating at least one thread for the at least one configuration query;
- executing the at least one thread in parallel to produce at least one query result; and
- presenting the at least one query result to a common results table;
- wherein the at least one query result is presented in a common format in the common results table.
12. The method of claim 11, further comprising:
- sorting the at least one query result in the common table.
13. The method of claim 11, further comprising:
- balancing a loading of the threads over at least one server to provide scalability for hosting multiple products,
- wherein the at least one thread is executed independently in the at least one server.
14. The method of claim 13, further comprising:
- clustering common search requests to provide scalability for supporting multiple users.
15. A method for creating a configuration table, comprising:
- identifying at least one supplier having at least one content file, wherein the at least one content file describes supplies provided by the at least one supplier;
- generating a supplier content table having a de-normalized structure from the at least one content file for the at least one supplier,
- identifying a buyer of the supplies listed in the at least one content file;
- generating a supplier configuration for the buyer that matches a pricing structure of the buyer in the supplier content table based on an identity of the buyer; and
- loading the supplier configuration in the supplier configuration table.
16. The method of claim 15, further comprising:
- determining at least one supplier available to the buyer;
- generating a supplier content table for each additional supplier;
- generating a supplier configuration for each additional supplier based on the buyer's access to one or more pricing structures in the supplier content table; and
- updating the configuration table by loading the supplier configuration for each additional supplier into the supplier configuration table,
- wherein a supplier configuration identifies structures in a supplier content table are available to search by a buyer.
17. The method of claim 15, further comprising dynamically adding one more structure to the content table for expanding a supplier offering.
18. The method of claim 15, further comprising: wherein the view identifies at least one entry in at least one structure of the supplier content table that is available for search to the buyer.
- determining a view associated with the buyer; and
- entering the view in a view structure in the supplier content table,
19. The method of claim 15, wherein generating a supplier content table comprises:
- denormaling a relational database model of the at least one content file to generate a column structured table;
- wherein the denormalizing includes converting at least one relational table in the relational database model into columns in the column structured table, wherein the at least one relational table represents the at least one content file.
20. The method of claim 19, wherein the supplier configuration is a column formatted table having a content column, a price column, or a view column, wherein the columns are the structures of the supplier content table.
Type: Application
Filed: Oct 11, 2006
Publication Date: Oct 11, 2007
Applicant: Enporion, Inc. (Tampa, FL)
Inventor: James A. Garcia (Newark, CA)
Application Number: 11/548,440
International Classification: G06F 17/30 (20060101);