System and method of database creation through form design

A system and method of generating a database schema from a graphical user interface used to create a form. The embodiments discloses the system that utilizes a drag and drop application that allows for configuration of a plurality of forms. These forms can then be placed in a graphical flow that will dictate the order of the forms. Through its graphical user interface, the system is able to gather information on field structure, flow among form elements, element identification, among other embodiments. This information allows the system to automate the creation of the database schema without user intervention.

FIELD OF THE INVENTION

The present invention relates to the field of database design specifically database schema creation via a graphical user interface

BACKGROUND OF THE INVENTION

Traditional programming done in computer systems needs to store information in some kind of permanent storage. While this can be accomplished on simple text files, the complexities of today's information requires a more structured form of storage. Some of the technologies that have been created to facilitate this task range from XML and JSON formats to full blown relational database management systems and NoSQL databases.

The multiplicity of programming languages and their respective complexity coupled with the complexity of permanent storage systems have given rise to specialization where management of storage systems and programming have to be done by different individuals. Some efforts have been made with the WYSIWYG (What You See Is What You Get) editors have enabled the automatic generation of programming code related to forms and to hide the complexity of systems programming for the most common tasks. The versatility of these systems even allow the user to connect the forms to already existing databases. Unfortunately, the number of state of the art systems that utilizes WYSIWYG editor to generate forms that connect to databases and also integrate schema generation is extremely limited.

DESCRIPTION OF THE PRIOR ART

What can be considered the best representation of the current state of the art in WYSIWYG that fuses both the code generation and database integration that includes schema generation is U.S. Pat. No. 8,683,315 B2. While this state of the art representative includes database schema generation, it is severely limited and is only useful under extremely restrictive instances. The invention allows the creation and modification of a database schema.

The schema generation on the U.S. Pat. No. 8,683,315 B2 follows from the schema generation interface where “the user desires to use a preexisting schema for a common type of form . . . then the user selects a schema suggested by the system based on the form input elements included on the form interface”. In addition the system may “scan the form elements and determine that the user has a high probability of implementing a guest book, for example, in which case a schema or list of guest book Schemas is presented to the user for selection”. Alternatively, “the system may present code generation interface at any point in the process and the user may, for example, edit the generated schema and/or SQLfile.”

The embodiments described above by this representative of the state of the art is reliant on predefined templates to be matched and does not provide a true SQL schema generation which may contain form fields outside of those provided by the templates. The patent representative also is mute on how does the application generates those templates. Based on the information provided, someone skilled in the art will likely come to the conclusion that it needs a database programmer to pre-design the templates. This defeats the purpose of automatic generation and truly automated system behavior by shifting the problem to pre-design by a person with database knowledge.

Truly automated systems in schema generation presents some real challenges that are non-obvious and difficult to solve. The first is to enable a system to generate the schema by gathering information that must be inferred from user generated fields. This is a challenge since a field could be well written as “name”, “last name” or alternatively can be written as “al”, “a2” respectively. The second challenge is even if the fields are properly labeled in a form, the relationships may not be evident such as “name” “name of spouse” and “sex”. The last example, may refer to the sex of the person filling the form field name.

The state of the art representative is limited to relational databases and does not cover other forms of database such as semi structured document databases such as XML databases and other databases such as NOSQL databases.

SUMMARY OF THE INVENTION

The present invention overcomes the limitations of the Prior art in the generation of the database schema when using a WYSIWYG to construct forms. Therefore, one of the objectives of the present invention is to provide a system that generates the database schema from information gathered by inferences from user generated fields on the WYSIWYG

Another objective is to allow the system to guide the user through its WYSIWYG interface to properly label form fields that can be used to infer information as to the relationship between fields on the form. This information can then be leveraged by the system during the process of database normalization or storage optimization on semi structured documents such as XML or JSON.

Another objective is to allow the system to gather information on how multiple forms interact with one another in a transactional flow that consists of multiple forms and decisions among the forms shown to the user based on the fields that are filled by the user.

The invention itself, both as to its configuration and its mode of operation will be best understood, and additional objects and advantages thereof will become apparent, by the following detailed description of a preferred embodiment taken in conjunction with the accompanying drawing.

The Applicant hereby asserts, that the disclosure of the present application may include more than one invention, and, in the event that there is more than one invention, that these inventions may be patentable and non-obvious one with respect to the other.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Those with skill in the computing arts will recognize that the disclosed embodiments have relevance to the automatic creation of a database schema. Such creation can have a meaningful impact on the amount of hardware necessary to execute the database in an efficient manner. Especially, the efficient implementation depends on the need to conserve disk space and the amount of virtual memory, caching and Random access memory required. This is also crucial if the conceived embodiments are not just implemented on high end expensive system but embedded systems and other relevant computing architectures. In addition, the functionality of the subject matter of the present application can be implemented in software, hardware, or a combination of software and hardware. The hardware portion can be implemented using specialized logic; the software portion can be stored in a memory or recording medium and executed by a suitable instruction execution system such as a microprocessor.

FIG. 1presents a depiction of the overall system of an embodiment of the proposed system where a person utilizes a computer system1to interact with an application interface2. The application interface2may completely or partially reside on the computer system1. The application interface2provides a graphical representation of a flow3. Once the user finishes configuring the graphical representation of flow3, the user can communicate through a communications line4to a remote location5that is a site that houses a server system6. The communications line4is typically embodied to operate in a networked environment using logical connections to one or more remote computers, such as a remote server system6. An alternate embodiment may forego the remote location in favor of being located locally close to the user in the same facility. The communications line4include a local area network (LAN) and a wide area network (WAN), but may also include other networks. Such networking environments are commonplace in offices, enterprise-wide computer networks, intranets and the Internet and is well known to those skilled in the arts. For example, in the subject matter of the present application, the computer system1may comprise the source machine from which data is being migrated, and the remote server system6may comprise the destination machine. Note however that source and destination machines need not be connected by a network or any other means, but instead, data may be migrated via any media capable of being written by the source platform and read by the destination platform or platforms. When used in a LAN or WLAN networking environment, the computer1is connected to the LAN through a network interface or adapter. When used in a WAN networking environment, the computer1typically includes a modem or other means for establishing communications over the WAN, such as the Internet. The modem, which may be internal or external, may be connected to the system bus via the user input interface or other appropriate mechanism. In a networked environment, program modules depicted relative to the computer1, or portions thereof, may be stored in the remote memory storage device. By way of example, remote application programs may reside on memory device. (twill be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers may be used.

The server system6in its typical embodiment is made up of a server7or a plurality of them. The server system7typically consists of a CPU system8that connects to a bus9. The bus9also connects to memory modules10and 1/0 device connector11. 1/0 connector11connects to a storage disk12. Such representation of storage disk12can have an alternate embodiment such as solid state device, GPU RAM, optical disk or other storage facility.

FIG. 2displays a graphical representation of the major components of the proposed system that will process a generic deigned flow including the proposed database schema generation. The system starts with the interface system2that has a functionality icons14that have the configuration that the system will execute and are connected via a link16together to form an execution program20. Once the execution program20is finished in its design the program will be forwarded to an execution manager22. The execution manager22will reside on the remote server system6. The execution manager22will produce an execution map23based on the execution program20. The execution map23contains an execution matrix24that will store the order of the execution for the execution program20. Each entry in the execution matrix24is assigned an execution slot25that can be filled with an execution entry26that corresponds to functionality icon14. Once the execution map23is completed it is passed to a controller27that also resides on server remote system6. The controller coordinates the execution with an execution engine28across the cloud environment29. The controller27communicates to an execution engine coordinator30that resides on one of n remote server system6of cloud environment29. The execution engine coordinator30uses a hardware selector31to discriminate which component of remote server system6. For example, hardware selector31can choose between execution hardware including but not limited to CPU, GPU multithreading or other processing technology and hard drive, optical media or other storage medium. Once hardware selector31chooses the particular processing technology, the hardware selector31selects a hardware optimizer32which coordinates with a hardware software module33that contains the necessary routines to interact with an hardware34. An alternate embodiment might also include a non-cloud environment where all components run on a single system and includes the specified components to run on a single system. Such alternate embodiment can be easily be conceived by someone skilled in the art from the already disclosed description.

FIG. 3displays the application interface2with specific functionality icon14and link16together to form the schema creation in an execution program20. Each of the icon14represents a configuration interface that are interlinked together via link16. The application interface2takes the configuration from each interface represented via the plurality of icon14and converts it into an executing program. This program is passed then to execution manager22to execute the program in remote server system6. The figure shows a plurality of icon14that are placed in their respective execution slot25depicted by the black lines surrounding each functionality icon14. The application interface2contains icons button40that can then be placed on a canvas41and in this way become functionality icon14and be connected by link16. The plurality of icons14in their respective execution slot25that together constitute a transaction template in a graphical form start out with an icon42which is an initiator in the desired execution order. The icon42is the icon responsible in bringing the configuration menu for the initial form and the database creation. Additionally the icon42is the entry point of a user transaction. An icon43placed after icon42receives the information from the created database and can map the information submitted from the form created and insert it into the database. After icon43comes icon44that extracts information from the database and shows the results to the user through icon45. The graphical representation of the transaction template ends when the icon45gives way to a transaction terminator icon46which signal the application to end.

FIG. 4presents the form interface where the user configures the form fields and the respective positioning within the form. The form interface50is presented to the user when he clicks on the icon42of the application interface2. The form interface50has a top menu51that allows the user to have an editor mode a preview of the final design a save functionality and delete functionality. The form interface50also has a form name field where the user identifies the name of the form. The form interface50further contains a form name52and an icon buttons53that are form elements such as a text field54that can be dragged and dropped into a canvas55. Fields such as text field54are accepted on the canvas55as long as they fall into a section56. Additional sections can be added besides section56such as a section57and other sections as deemed relevant by either application defaults or user addition.

From the interface a database can be generated such that the form name extracted from form name52and placed in a database schema58as a main table name59. The main table59contains relevant columns such as the form name primary key60. Other column elements that can be part of the embodiment are but not limited to timestamp, form metadata, section metadata among others. A table61can be created based on a respective section56. Table61can then be identified by giving a table name62based on section56. A Primary key63identifies each entry on table61and is a required column to identify each entry made through the form represented in canvas55. The table61also needs to include a foreign key64that maintains the relationship with main table59and the other tables in the database for each entry. The table61also includes data field65which inserts the information entered on the input area such as text field54by the user via form represented by canvas55. The data entry is not limited to text field54but any additional data entry representation given by the operations supplied by icon buttons53.

FIG. 5presents a flowchart of the field information validation process. The figure start with a step70where the user drag and drops icon buttons53with the desired input field representation on canvas55. When the user drops the desired input field on step70a step71is triggered where the relevant information fields are shown to the user to enter the name for the field and additional space for metadata. Said name and metadata are used to identify the field on canvas55. The text supplied in step71is broken down into individual words in a step72. The individual words obtained in step72are validated in a step73with a dictionary to see if they are acceptable words in the language. Additional validation steps can be carried out in an alternate embodiment before step73such as minimum length of 4 characters such as to avoid words such as ‘is’, ‘but’, ‘the’ among other words with minimal descriptive use. The step73is followed by a decisional step74that instructs the process on negative results to go to a step75where the user is presented with a notification. The notification informs the user that the terms are not in the dictionary and the step75guides the user to enter additional text or modify the entry in step71. If step74positively asserts that words are in the dictionary a step76is executed where the entries are searched for synonyms, hypernym/hyponym relationship among the terms. The embodiment is not limited to just synonyms, hypernym/hyponym but includes additional relationships such as antonyms, sense disambiguation among other lexical and even semantic relationships such as semantic role labeling for metadata and fields. The relationships found in step76are used in a decisional step77where the words corresponding to different fields are compared for their respective relationships among each other. If decisional step77is positive, a grouping step78is implemented where fields having relationships among them are grouped together. Step77answered in the negative and the step78lead to a decisional step79that if the user is not finished with the form it leads back to step70, else it goes to the next steps of the process in a step80.

FIG. 6shows the graphical user interface and a vector space representation.FIG. 6shows an alternate representation of form interface50. Form50shows a field91that has been labeled ‘customer name’. Form50also includes a field92labeled ‘customer last name’, a form field93labeled ‘customer address I’, a field94labeled ‘customer address 2’ and a field95labeled ‘customer zip code’. All fields' labels of form50are then parsed into individual words and an inverted index96is created. Field91is converted into a vector97, field92is converted into a vector98, field93is converted into a vector99, field94is converted into a vector100and field95is converted into a vector101. Vectors97,98,99,100and101are created by checking if the respective entry in the inverted index is part of the label. If the index entry is found in the label then a 1 is inserted else the entry is assigned a value 0 for that vector. The vectors that score close together can be assigned into a group102.

FIG. 7shows a graphical user interface that displays multiple forms. A graphical user interface110displays an interface111. The interface111displays a form112. Several forms112can be displayed in the interface as needed to generate a complex data entry process. All instances of form112can be brought under a project name113and stored in a database under the project name113identifier. New forms can be added to the project under project name113by using an addition button114.

FIG. 8displays a typical transactional flow with multiple forms. A user interface130displays a transactional flow131. The transactional flow direction is represented by an arrow132. The transactional flow has an icon133, an icon134and an icon135that represent the insertion on the flow of form interface50and its underlying execution functionality of displaying its respective form for input. An icon136represents the configuration for directing the system to accept the form information from the forms and insert them into the database. Such decoupling of icon133, icon134and icon135that display the form and icon136that inserts the database is representative but not limited to architectures such as client server communication m web programming. In such communication architecture, the display is managed independently as client side programming and the functionality that executes the processing of information is managed by the server.

FIG. 9is a flowchart of the database schema creation process on multiple forms used in a transactional flow. The process starts with a single page being received for processing in a step150that does the field information validation process described byFIG. 5. A step151follows step150where the vector analysis ofFIG. 6is carried out. Following step151a step152verifies if there is more than one form to process in the programming that executes the processing of graphical user interface110ofFIG. 7. If validation in step152determines that there are multiple forms, then the process will take a path153to a stopping criteria in a step154. If step154is negative, then it will return to the beginning of the flow and repeat until all forms are processed. If step152is negative it will take a path155that will join with a path156coming from step154that ends in a step157that will create the initial database for a single or multiple forms. After generating the initial database in step157a step158will monitor user interface130to determine if the user completed the intended flow and will continue in a loop159until the flow on interface130is finished. Once the flow is finished as validated in step158a step160will analyze the flow and determine the position of the forms within the flow. This analysis will assign a high probability that forms in the beginning of the flow will generate primary keys which will act as foreign keys in subsequent forms if they are connected in a path dependent fashion. A step161following step160will carry out the grouping through vectorization and validation through the multiple forms that are present in the flow. Based on the analysis of step160and step161a step162will reorganize the tables by altering their structure or adding foreign keys to reflect the new relationships. These new relationship changes can embody doing normalization based on matching similar fields, adding foreign keys to subsequent forms and grouping sections or separating them based on vectorization and synonyms, hypernym/hyponym relationships. This embodiment is not limited only to relational databases but can be utilized in other database types such as XML databases or similar databases.

The invention is not limited to the precise configuration described above. While the invention has been described as having a preferred design, it is understood that many changes, modifications, variations and other uses and applications of the subject invention will, however, become apparent to those skilled in the art without materially departing from the novel teachings and advantages of this invention after considering this specification together with the accompanying drawings. Accordingly, all such changes, modifications, variations and other uses and applications which do not depart from the spirit and scope of the invention are deemed to be covered by this invention as defined in the following claims and their legal equivalents. In the claims, means-plus-function clauses, if any, are intended to cover the structures described herein as performing the recited function and not only structural equivalents but also equivalent structures.

All of the patents, patent applications, and publications recited herein, and in the Declaration attached hereto, if any, are hereby incorporated by reference as if set forth in their entirety herein. All, or substantially all, the components disclosed in such patents may be used in the embodiments of the present invention, as well as equivalents thereof The details in the patents, patent applications, and publications incorporated by reference herein may be considered to be incorporable at applicant's option, into the claims during prosecution as further limitations in the claims to patently distinguish any amended claims from any applied prior art.