JP2010191628A - Data definition support system and method for relational database - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To solve a problem wherein it is necessary to perform definition work of data to an individual master table by individual master table. <P>SOLUTION: One or more attributes are taken out from each of a plurality of master tables and are composed according to a composition rule described in a setting file, and one virtual table is generated. Definition input of the data is received through a work screen corresponding to the virtual table, and thereafter the received data are registered in the corresponding virtual table. The registered data to the virtual table are made to be reflected in one or more master tables each having the attribute corresponding to the registered data. <P>COPYRIGHT: (C)2010,JPO&INPIT

Description

  The present invention relates to a system and method for supporting management work of data definitions used in a relational database. In particular, the present invention relates to a system and method for supporting data definition for a plurality of master tables having high work relevance.

  At present, relational databases are widely used in computer systems. A relational database is a form of database that manages a group of related data in a table format. A field for identifying data attributes is arranged in the horizontal axis direction of the table, and a record for identifying a set of data corresponding to the plurality of fields is arranged in the vertical axis direction.

  In a relational database, a reference relationship can be set between fields of a plurality of tables. In this specification, a table that is referenced from another table for various purposes is called a master table.

  Patent Documents 1 and 2 disclose a mechanism for supporting data registration and editing operations on the master table. For example, Patent Document 1 discloses a mechanism for displaying data as a reference destination in a separate window and inputting the data from the separate window when inputting data in a field having a reference relationship. Patent Document 1 realizes reduction of the number of key inputs and avoidance of contradiction with reference data by this mechanism. For example, Patent Document 2 discloses a mechanism for reducing the burden of creating an input data check program for each master table.

Japanese Unexamined Patent Publication No. 7-219824 JP 2008-70969 A

  A master table used by a manufacturing execution system (hereinafter referred to as “MES”), a management resource planning (hereinafter referred to as “ERP”) system, and other systems is a basic table used in common for various purposes. It is. For this reason, it is finely normalized. Therefore, a plurality of master tables are also required for one function of the system, and even closely related master tables exist separately.

  For these reasons, the management of the data definition of the master table is very difficult for the field worker to understand and is a burden. For example, when a function is to be used in a business procedure such as “inventory check” or “production plan change” supported by MES or ERP, it is necessary to register information on the function and its parameters in a relational database.

  However, there are usually various types of functions, and the number of arguments is not fixed. Therefore, when registering such information in the relational database, it is common to separate it into a “function table” and a “parameter table”. In this case, for example, the function table stores information such as the location of the library file that stores the function used in the business and the entry point thereof. On the other hand, the parameter table stores information on the argument.

  As you can see when writing a program, functions and arguments are usually written together. Therefore, even if the functions and parameters must be described in different tables due to the configuration of the master table, it is common to define them collectively at the stage of defining data.

  However, even if it is natural information to think together, it is often necessary to manage the master table separately. For example, when managing information on valves and pumps. The valve and the pump may have an N: 1 connection relationship. In this case, when managing on a relational database, it is easier to use a separate master table. However, at the time of defining the data, it is easier to work if the data can be defined at once based on the piping instrumentation diagram or the like.

  Thus, even when managed as a separate master table in relation to the structure of the relational database, there is information that is more convenient if it can be defined as a group at the time of data definition. The master table has such characteristics.

The devices disclosed in Patent Documents 1 and 2 described above can also be used when defining data in such a master table.
For example, consider a case where the technique disclosed in Patent Document 1 is used for data definition of the function table and its parameter table. Here, let us consider a case where a function is defined first and then a parameter is defined. In the case of this technique, a function list based on the reference relationship is displayed in a separate window separately from the data definition screen for the parameter table. Therefore, the user defines the data by selecting a function associated with the parameter from this separate window.

  However, in this method, only a function that can be associated can be viewed from one parameter. For this reason, it is impossible to know all the parameters related to the function. That is, it is not possible to grasp the whole picture including all parameters related to the function. There is a similar problem when applied to the relational database related to the equipment configuration described above.

To solve this problem, a program can be created that can collectively define records in several types of tables that are inseparable from each other.
However, as disclosed in Patent Document 2, creating a separate program for each case related to the definition of the master table is too burdensome in terms of development cost.

  Patent Document 2 aims to reduce the burden of creating such individual programs. However, this method eliminates the need to create a check program to be used when data is input to an individual data table. That is, as described above, it is not possible to collectively define records of a plurality of tables having an inseparable relationship.

  Applicant has a mechanism that allows data definition work for attributes distributed in multiple master tables on a relational database to be executed through a single work screen, despite the close relationship at the time of data definition Propose.

  Specifically, a mechanism composed of the following three steps is proposed. That is, a highly relevant attribute is extracted from a plurality of master tables, synthesized, a virtual table is generated, and a work screen corresponding to the virtual table is presented to the worker, and data is defined through the work screen. A second step of accepting an input and registering it in the virtual table; a third step of reflecting the data registered in the virtual table in corresponding data of one or more master tables having attributes corresponding to the data; We propose a mechanism consisting of

  By adopting the mechanism described above, even when highly relevant data exists in multiple master tables when data is defined, the user can define data through the virtual table work screen that combines these data into one. Work can be done at once.

  Further, the data registered in the virtual table through the work screen is automatically reflected in the corresponding data of one or more master tables having attributes corresponding to each data when the work is completed. With this function, data consistency among a plurality of master tables can be ensured.

It is a figure which shows the functional block structure of the data definition assistance system which concerns on an example. It is a figure which shows the example of a structure of three master tables used as the object of data definition. It is a figure which shows the example of a structure of the setting file used with a data definition assistance system. It is a figure explaining the correlation of four types of objects. It is a figure which shows the first half example of the production | generation flowchart of a virtual main table. It is a figure which shows the second half example of the production | generation flowchart of a virtual main table. It is a figure which shows the structural example of the virtual main table produced | generated by step 512. FIG. It is a figure which shows the example of a flowchart explaining the detailed operation | movement of the subroutine 606. FIG. It is a figure which shows the example of a selection screen of the setting file used in a data conversion part. It is a figure which shows the structural example of the data object produced | generated by the subroutine 606. It is a figure which shows the conceptual structural example of a virtual worker table. It is a figure which shows the example of conceptual structure of a virtual title table and a virtual affiliation table. It is a figure which shows an example of the definition user interface for virtual worker tables. It is a figure which shows an example of the definition user interface for a virtual title table. It is a figure which shows the example of a production | generation flowchart of the display screen of a virtual main table. It is a figure which shows the example of a flowchart used for the data input to a virtual table. It is a figure which shows the example of input of the new data with respect to the property display part of a virtual table. It is a figure which shows the example of a flowchart showing the process sequence which reflects new data in a virtual subtable when new data is input into a virtual main table. It is a figure which shows the example of a conceptual structure after adding a new record to the virtual worker table as a virtual main table. It is a figure which shows the example of a flowchart showing the process sequence which preserve | saves data from a virtual main table to a spreadsheet. It is a figure which shows the example of a conceptual structure after a new record is added to the virtual title table as a virtual subtable. It is a conceptual diagram showing insertion of the attribute information object corresponding to description of step 3 of the example 2 of a form. It is a figure which shows the example of a change input of the existing data using the property display part of a virtual table.

  Hereinafter, an example of a data definition support system according to the invention will be described. Needless to say, the present invention is not limited to the embodiments and description to be described later, and can be applied to other modes combined with known techniques.

(1) Example 1
(A) Overall Configuration of System FIG. 1 is an overall configuration diagram including a relational database system and its peripheral elements. The relational database system includes a computer 101 and its input devices (mouse 109, keyboard 110). Note that the worker 111 operates the relational database system using the input device.

Needless to say, the hardware of the computer 101 includes an arithmetic logic unit, a control circuit, a storage device, and an input / output device (including a monitor).
On the other hand, the computer 101 is functionally composed of a spreadsheet sheet 103 of spreadsheet software, data definition support software 102, and an input screen 108.

Note that the spreadsheet sheet 103 in the form example has a one-to-one correspondence with the master table of the relational database.
The input screen 108 is a user interface screen generated by the data definition support software 102. Of course, the input screen 108 is displayed on the screen of the display device.

  The data definition support software 102 includes a data conversion unit 104, a definition support service unit 105, and a setting file 106. The setting file 106 describes rules for synthesizing a virtual table from the master table. Incidentally, the setting file as data is stored in the storage area of the computer 101. The data conversion unit 104 generates a virtual table 107 from the spreadsheet sheet 103 based on the rules described in the setting file 106. The virtual table 107 corresponds to a virtual main table and a virtual sub table described later. In this embodiment, the virtual table 107 is generated from the spreadsheet sheet 103 of spreadsheet software. However, it can also be generated from the master table of the relational database instead of the spreadsheet sheet 103.

(B) Example of master table as data definition work target In this embodiment, description will be made assuming that there are three master tables as definition targets. Of course, there may be four or more master tables. FIG. 2 shows a structure example of the spreadsheet sheet 103 corresponding to the three master tables. That is, consider the operator table 201, the post table 202, and the affiliation table 203. FIG. 2 shows the tables corresponding to the worker table 201, post table 202, and affiliation table 203 with reference numerals (a), (b), and (c).

  The data structure of each table is common, and the first and second rows constitute the information display unit 204, and the third and subsequent rows constitute the data unit 205. The attribute name is described on the first line, and the type code is described on the second line. In addition, the data of the relational database is used in the form of a table in each row constituting the data unit 205.

(C) Data Structure of Setting File Data included in the setting file 106 described with reference to FIG. 1 will be described with reference to FIG. In the setting file 106, a plurality of master table combining rules that are considered to be better to perform data definition work together are described according to the work purpose. That is, the data definition support software 102 stores a plurality of setting files 106 corresponding one-to-one for work purposes. Therefore, at the time of data definition, one appropriate setting file is selected according to the data to be defined. Note that the composition rules described in the configuration file are free. Of course, a collection of all master tables having a reference relationship with a certain master table can be automatically set as a collection of master tables for definition work. Even if such a setting method is used, a user-friendly setting file can be created in many cases.

  Returning to the description of FIG. FIG. 3 shows an example of the setting file 301 corresponding to the “worker information management” function of a certain system. In the setting file 301, setting information of the use table, configuration information of the virtual table, and setting information of the display attribute are described.

  First, usage table setting information will be described. In the setting information of the use table, information of a data table (that is, a master table) necessary for using the MES function corresponding to the setting file 301 is described. In the case of FIG. 3, the usage table setting information is the number of master tables to be used, the name of the master table to be used, and information on whether each master table is used as a main table or as a sub table.

  In the case of FIG. 3, the second line “TABLE_SU: 3 (number of tables: 3)”, the third line “MAIN: SAGYOUSHA (worker table)”, the fifth line “SUB: YAKUSHOKU (position table)”, the ninth line “SUB: SHOZOKU (affiliation table)” corresponds to the setting information here. From this setting information, it is understood that the number of necessary tables is 3, the worker table 201 is used as the main table, and the post table 202 and the affiliation table 203 are used as the sub tables.

  Next, the configuration information of the virtual table will be described. In the configuration information of the virtual table, information for specifying the configuration of the generated virtual table is described. For example, which attribute of the sub-table is combined with the main table and the number of attributes to be combined are described.

  In the case of FIG. 3, the sixth line “GOUSEISU: 1”, the seventh line “ZOKUSEI: YAKUSHOKU_MEI (attribute: title)”, the tenth line “GOUSEISU: 2”, the eleventh line “ZOKUSEI: SHOZOKU_MEI (attribute: affiliation name) ) "Corresponds to the information here.

  Finally, display attribute setting information will be described. The display attribute setting information is information related to data display of the created virtual table, and is information referred to in the definition support service unit 105.

  In the case of FIG. 3, “HYOUZI: ˜ (display: ˜)” written in the fourth, eighth, and twelfth lines corresponds to the setting information here. How to use the setting information will be described in detail when the definition support service unit 105 is described.

  It is assumed that the database used in the embodiment has the following restrictions. Of each attribute of the data table, a part related to another table is assumed to be the same for all tables. For example, when the attribute name that refers to the job table 202 in the worker table 201 is “job code”, the corresponding attribute name in the job table 202 that is the reference destination is also “job code”. In a general database, this is not necessarily the case, but in the following description, it is assumed that the database is configured according to this rule.

(D) Data Structure Example of Virtual Table Next, the data structure of the virtual table generated by the data conversion unit 104 will be described. As shown in FIG. 4, the virtual table has four basic objects as a table object 401, a record object 402, an attribute information object 408, and a data object 404.

The table object 401 has a record object list 406 and an attribute information object list 407 as its members.
The record object 402 has a data object list 403 as its member.
The attribute information object 408 has a character string type code and a character string type attribute name as its members.

  The data object 404 has character string type attribute names and attribute values as its members. However, an attribute value having the same type as that described in the type code of the attribute information object 408 having the same attribute name as the attribute name of the data object 404 is given. The data object 404 further has a data object list 409 when the condition is satisfied. The condition here is that the attribute information object 408 having the same attribute name as the attribute name of the data object 404 is “container type”. In this case, the data object 404 has a data object 405 linked to the data object list 409.

  Finally, the correspondence between the data structure of the virtual table and the data table will be described. When the data table is viewed as a spreadsheet sheet shown in FIG. 2, the table object 401 corresponds to the sheet itself of each data table. An attribute information object 408 corresponds to each cell of the information display unit 204 located in the first and second rows of each data table. The record object 402 corresponds to each row after the third row of each data table, and the data object 404 corresponds to each cell constituting the data portion 205 of each data table.

(E) Virtual Table Generation Processing Example (e-1) Virtual Main Table Generation Next, referring to FIGS. 5 and 6, the data conversion unit 104 is referred to as a main table virtual table (hereinafter referred to as “virtual main table”). ) Will be described. 5 is the first half of a series of processing procedures, and FIG. 6 is the second half of a series of processing procedures. In the following description, it is assumed that a virtual table for data definition necessary for executing “worker information management” of a certain system is generated. However, it is assumed that the data shown in FIG. 2 has already been input to each master table related to the setting file 106.

  This generation process of the virtual main table is started through selection input of the setting file 106 (step 501). Specifically, it is started through a selection input on the function selection screen 901 shown in FIG. 9 on an operation screen (not shown). The function selection screen 901 displays a list of functions as data definition candidates.

  This time, it is assumed that the operator selects the “worker information management” option 902 and presses the enter button 903. Upon receiving this event, the data conversion unit 104 reads a setting file corresponding to the determined option. In this embodiment, it is assumed that the setting file corresponding to “worker information management” is the same as the setting file 301 shown in FIG. Then, the data conversion unit 104 acquires setting information from the setting file 301 (step 502). Since “TABLE_SU: 3 (number of tables: 3)” is written in the second line of the setting file 301, the data conversion unit 104 generates three table objects (step 503).

  Thereafter, the data conversion unit 104 enters a repetitive process (step 504) and generates one attribute information object (step 505). Since the main table is described as the worker table 201 in the setting file 301, the data conversion unit 104 acquires attribute information (attribute name and type code) from the first column of the worker table 201 (step 506). In the case of the worker table 201 in FIG. 2, “worker code” and “character string type” are described as attribute information. Therefore, the data conversion unit 104 determines whether or not the same attribute name as the “worker code” exists in the sub-table (step 507). That is, the data conversion unit 104 determines whether or not the post name table 202 and the affiliation table 203 described as sub-tables have the same attribute name as the attribute name “worker code”. If the attribute name is suffixed with a number, the data conversion unit 104 gives the attribute name with the number deleted to the subsequent step.

  In the post table 202 and the affiliation table 203 in FIG. 2, the attribute name “worker code” does not exist. Therefore, the data conversion unit 104 obtains a negative result in the process of step 507. In this case, the data conversion unit 104 adds the attribute name “worker code” and the type code “character string type” to the attribute information object 408 (step 509).

  Thereafter, the data conversion unit 104 determines that one of the table objects 401 created in step 503 is to be used as a virtual main table, and the attribute information object 408 generated in step 509 is added to the attribute information object list 407. Register (step 510).

  Thereafter, the data conversion unit 104 repeats a series of loop processes (steps 504 to 510). That is, the data conversion unit 104 repeats the process of generating the attribute information object 408 and writing the attribute name and type code, and registering the attribute information object 408 after the data writing into the attribute information object list.

  However, in the case of the third loop process (N = 3), a change occurs in a part of the loop process. In this case, the attribute name “posture code” is read from the third column of the worker table 201. This “position code” also exists in the position table 202 of FIG. Therefore, the data conversion unit 104 obtains a positive result in the determination process in step 507.

  In this case, the data conversion unit 104 sets the type code of the attribute information object 408 to “container type” and the attribute name to “position 1” (step 508). The attribute name here is generated in a format in which a number is added to the subtable name. A natural number is used for the number. If an attribute name with the same subtable name has already been registered, a value obtained by adding 1 to the maximum number used at the time of processing is used. This time, since the position code appears for the first time, the number “1” is used.

  In the case of the fourth and fifth loop processing (N = 4, 5), the data conversion unit 104 performs the same operation as the third loop processing. That is, the data conversion unit 104 registers the type code “container type” and the attribute name “affiliation 1” in the attribute information object 408 corresponding to the affiliation code 1, and the type code “container” in the attribute information object 408 corresponding to the affiliation code 2. Register “container type” and attribute name “affiliation 2”.

  The data conversion unit 104 repeats the series of loop processes until the number of times N is equal to the number N of columns in the main table (that is, the worker table 201). Thereafter, the data conversion unit 104 creates the same number of record objects 402 as the number of worker tables 201 (step 511), and adds it to the record object list of the table objects (step 512). A conceptual diagram of the virtual main table completed up to step 512 is shown in FIG.

  Subsequently, the data conversion unit 104 enters the repetition process again (steps 601 and 602). Note that the repetitive processing in step 601 is executed for N lines constituting the data portion 205 of the worker table 201. Further, the iterative process in step 602 is executed for M columns constituting the data part 205 of the main table. That is, this processing corresponds to processing for registering data of each cell constituting the data portion 205 of the main table in the data object.

  First, the data conversion unit 104 generates one data object (step 603). Next, the data conversion unit 104 determines whether or not the attribute name of the Nth attribute information object is a container type (step 604). In the example of FIG. 7, the first attribute name of the attribute information object is “character string type”. Therefore, the data conversion unit 104 obtains a negative result and puts “character string type” in the attribute name of the generated data object. Further, the data conversion unit 104 inputs the value “S001” in the first row and first column of the data portion of the worker table 201 as the attribute value of the generated data object (step 605). The data conversion unit 104 registers the created data object 404 in the data object list 403 of the first record object 402 (step 607).

  The data conversion unit 104 repeatedly executes this series of processing. When N = 3, the data conversion unit 104 refers to the third attribute information object 702 in FIG. The attribute name of this attribute information object 702 is a container type. Therefore, the data conversion unit 104 obtains a positive result at step 604 and executes the subroutine of step 606.

  FIG. 8 shows details of processing contents executed in the subroutine of Step 606. The data conversion unit 104 generates the data object again in the course of executing this subroutine. That is, the data object 405 is generated. Therefore, in order to distinguish the data objects, the data conversion unit 104 uses the name of another data object that has already been created as the data object 1. Further, among the data objects included in the data object 1, a data object 2 into which the attribute name and attribute value of the main table are inserted. Further, among the data objects included in the data object 1, a data object in which the attribute name and attribute value of the sub table described to be added to the main table in the setting file 301 is set as the data object 3.

  FIG. 10 shows the relationship between the data objects 1, 2, and 3. In the case of FIG. 10, there is one data object 3, but the number can vary depending on the setting of the setting file 301.

  Subsequently, the contents of the subroutine processing in step 606 will be described. First, the data conversion unit 104 adds the attribute name “title 1” of the attribute information object 702 in the third column (for N = 3) of the virtual main table 701 to the attribute name of the data object 1 to be processed ( Step 801). Next, the data conversion unit 104 acquires the table name “position” from the attribute name “position 1” of the attribute information object 702 (step 802).

  Subsequently, the data conversion unit 104 newly generates a data object 2 (step 803). Here, the data conversion unit 104 inputs “position code” that is the attribute name in the third column of the worker table 201 to the attribute name of the data object 2, and the attribute value of the data unit 205 in the worker table 201 is input to the attribute value. “Y001”, which is data in the first row and the third column, is input (step 804).

  Subsequently, as shown in FIG. 10, the data conversion unit 104 registers the created data object 2 (ie, 1002) in the data object list 1000 of the data object 1 (ie, 1001) (step 805).

  Next, the data conversion unit 104 refers to the sub-table whose name has been acquired in Step 802, and generates as many data objects 3 as the number of attributes specified to be added in the setting file. In this example, the name of the sub-table acquired in step 802 is “title”, and the attribute of the title table to be added is “title name” as described in the seventh line (FIG. 3) of the setting file 301. Only. Therefore, the data conversion unit 104 in this example creates only one data object 3 (step 806).

  Further, the data conversion unit 104 refers to the sub-table whose name has been acquired in Step 802, and selects a row that matches the attribute name and attribute value of the data object 2 (Step 807). From within the selected line, the data conversion unit 104 selects a line having the same attribute as the attribute specified to be added in the setting file 301, and acquires the attribute name and attribute value (step 807). Then, the acquired attribute name and attribute value are registered in the newly generated data object 3 (step 807). If there are a plurality of data objects 3, the same processing is executed for all of them.

  In the case of this example, the name of the subtable acquired in step 802 is “title”. The attribute name of the data object 2 is “title code” and the attribute value is “Y001”. Therefore, the data conversion unit 104 searches the post table 202. In the case of this example, the cell in the first row and first column of the data part constituting the post table 202 satisfies the condition of the attribute name and the attribute value. Therefore, the data conversion unit 104 selects the first line of the data part in the post table 202. The attribute specified to be added in the setting file 301 in the first line is “title” as described in the seventh line in FIG.

  Therefore, the data conversion unit 104 acquires the title “factory manager” from the first row of the data part in the title table 202. That is, the data conversion unit 104 inputs “title” as the attribute name of the data object 3 and “factory manager” as the attribute value. If there is no other attribute to be added, the processing in step 807 ends as described above. However, when there are other attributes to be added, the data conversion unit 104 generates the data object 3 by repeating the above-described processing, and stores it in the temporary list in the order of generation.

  Finally, as shown in FIG. 10, the data conversion unit 104 adds the generated data object 3 (ie, 1003) to the data object list 1000 of the data object 1 (ie, 1001) in the order of creation (step 808). FIG. 10 shows a data object that is completed by the processing from step 801 to step 808.

  Thus, in the data object list 1000 having the data object with the attribute name “title 1” and the attribute value “NULL”, the data object 2 with the attribute name “position code” and the attribute value “Y001” and the attribute name “title” A data object 3 of “name” and attribute value “factory manager” is registered.

When the subroutine processing step 606 is completed, the data conversion unit 104 proceeds to the processing of step 607. In this example, the data conversion unit 104 adds a data object to the data object list of the first record object (step 607). The data conversion unit 104 repeats the same processing thereafter. That is, the processing from step 601 to step 607 is repeatedly executed for the first row, second column, first row, third column,..., Fourth row, fifth column of the data portion of the worker table 201.
Then, when all the processes are completed, the data conversion unit 104 completes generation of the virtual main table (step 608).

  FIG. 11 shows a conceptual diagram of the virtual main table. In this embodiment, a virtual main table is created using the worker table 201 as a main table, and thus the completed table is represented as a virtual worker table in FIG. FIG. 11 shows that a data structure is added to the virtual main table in the middle of generation shown in FIG. In the case of FIG. 11, as shown in FIG. 10, the contents of the data object 1 including the data objects 2 and 3 are omitted except for the first record object (that is, the first line of the data portion). ing. Of course, the same applies to the second and subsequent record objects.

(E-2) Generation of Virtual Subtable When generation of the virtual main table is completed, a virtual table (that is, a virtual subtable) is also generated for the subtable. A sub-table designated to be added by the setting file 301 is created. In the case of this form example, a virtual table is generated for the position table and the affiliation table.

  At the time of generating the virtual sub-table, all the terms “main” appearing in the processing procedure shown in FIG. 5 and FIG. 6 are replaced with “sub”, and then branching steps 501 → 502 → 503 → 504 → 505 → 506. It is only necessary to execute a processing procedure without branching: → 509 → 510 → 511 → 512 → 601 → 602 → 603 → 605 → 607.

  Note that the table object generated in step 503 is used. By performing according to this procedure, a virtual table corresponding to the sub-table can be created. FIG. 12 shows a conceptual diagram of the virtual sub-table corresponding to the post table and the affiliation table.

(F) User Interface Screen for Data Definition Here, an example of a user interface screen used by the user at the time of data definition will be described based on FIG. Hereinafter, the user interface screen for data definition is referred to as a data definition window 1308.

  The data definition window 1308 is generated based on the setting of the setting file 301 as described above. In the case of the data definition window 1308 shown in FIG. 13, tabs 1301 and 1302 are prepared corresponding to each of the virtual tables. Of course, FIG. 13 shows a case where the worker table 201 is a main table and the post table 202 and the affiliation table 203 are sub-tables. That is, the user interface screen used for data definition of the virtual worker table shown in FIG. 11 is shown. In this example, the tab 1301 corresponding to the virtual main table is displayed on the leftmost side, and other tabs are displayed side by side on the right side.

  A list 1305 and a property display unit 1307 are arranged on each tab sheet of the data definition window 1308. In the list 1305, representative display attributes among the attributes constituting the corresponding virtual table are displayed as display items. By selecting this display item, the corresponding record object in the virtual table can be selected. In FIG. 13, the worker name in the worker table 201 is displayed as a representative. On the other hand, the property display unit 1307 displays data of each data object included in the record object selected from the list 1305. Details of the display method will be described later.

  In the data definition window 1308, a delete button 1303 and a setting button 1304 are also displayed. The delete button 1303 is used to delete the selected item (record object) when a confirmation operation for the button occurs when a specific item is selected from the list 1305. Thereby, a record can be deleted.

  On the other hand, the setting button 1304 is used to reflect a change in data displayed on the property display unit 1307 for the specific item selected in the list 1305 in the virtual table. That is, the setting button 1304 executes an operation of writing the data displayed on the property display unit 1307 to the virtual table when a confirmation operation for the button occurs. This operation corresponds to the operation of step 1609 in FIG.

  In the list 1305, an empty selection portion 1306 for generating a new item is displayed at the bottom line of the display item. When the selection portion 1306 is selected, a screen with all attributes blank is displayed as the property display portion 1307. The user can generate a new record in the virtual table by inputting the operation of the setting button 1304 after inputting data in the blank.

  FIG. 14 shows a display example of the virtual sub-table. As described above, the data definition window corresponding to the virtual sub-table is displayed corresponding to the tab 1401 different from the tab corresponding to the virtual main table. For this virtual sub-table, a list 1402 and a property display unit 1403 are displayed. The operation of the definition support service unit 105 relating to data input and the like will be described later.

(G) Processing Contents of Definition Support Service Unit (g-1) Data Definition Window Generation Processing Next, the procedure for generating the data definition window 1308 by the definition support service unit 105 will be described.

  FIG. 15 shows an example of the processing procedure of the definition support service unit 105 when creating the data definition window 1308 corresponding to the virtual main table.

First, the definition support service unit 105 reads the display attribute portion of the setting file 301 (step 1501). In the case of FIG. 3, “HYOUZI: SAGYOUSHA_MEI (display: worker name)” on the fourth line is the corresponding part. On the other hand, the attribute value of the data object whose attribute name is the worker name in the virtual worker table 1101 is four, “XXX”, “YYY”, “ZZZ”, and “WWW”.
Therefore, the definition support service unit 105 displays these four values in the list 1305 as shown in FIG. 13 (step 1502).

  Subsequently, the definition support service unit 105 refers to an arbitrary record object in the virtual worker table, and obtains the number of all data objects that do not have other data objects in the data object list. To do. In the case of the first row of the data portion in FIG. 11, there are a total of eight data objects, two data objects surrounded by a broken line 1102 and six data objects surrounded by a broken line 1103 (step 1503).

  From this result, the definition support service unit 105 displays the property display unit of the data definition window 1308 in 8 rows and 2 columns (step 1504).

  Subsequently, the definition support service unit 105 enters a repetitive process (step 1505). First, the definition support service unit 105 acquires the type code “character string type” and the attribute name “worker code” of the attribute information object registered first in the attribute information object list (step 1506). Then, the definition support service unit 105 determines whether or not the acquired type code is a container type (step 1507).

  In this example, the type code is not a container type. Therefore, the definition support service unit 105 displays the attribute name “worker code” in the first column of the property display unit (step 1508).

  When M = 3, the definition support service unit 105 acquires the type code “container type” and the attribute name “position 1” in step 1506. This time, the type code is a container type. The definition support service unit 105 acquires the attribute name of the data object included in the data object list of the data object having the same “title 1” attribute name. The data object may be included in any record object. This is because the data object is the same regardless of which record object is included.

  In this case, as can be seen from FIG. 11, “position code” and “position name”. When displaying in the first column of the property display unit 1307, “1” indicating that the attribute name of the referenced attribute information object is “position 1” is added, and “position code 1” and “position” are added. Name 1 ”(step 1509).

  Similarly, the same processing is executed until M = 8. The data definition window 1308 for the virtual worker table shown in FIG. 13 is created by this procedure. The list 1305 of the data definition window 1308 and the display screen of the property display unit 1307 are generated in this way.

  Next, a processing procedure for generating a display screen of the data definition window 1308 corresponding to the virtual sub table will be described. The flow of the processing procedure executed by the definition support service unit 105 when displaying the virtual sub-table is almost the same as that in FIG. Specifically, steps 1501 → 1502 → 1503 → 1504 → 1505 → 1506 → 1508 are flow without branching.

  In this case, the definition support service unit 105 newly adds a tab describing the name of the corresponding virtual sub-table to the data definition window 1308, and displays a list 1305 and a property display in the screen displayed on the tab. A unit 1307 is generated. FIG. 14 shows a data definition window 1308 of the virtual title table completed through this processing.

(G-2) Data Definition Processing Using Data Definition Window (i) New Data Definition Input Next, the processing procedure executed when data definition is input to the virtual table using the data definition window 1308 will be described. To do.
FIG. 16 shows an example of the processing procedure of the definition support service unit 105 when data input through the data definition window 1308 is reflected in the virtual table.

  Here, it is assumed that data relating to a worker is newly input to the virtual worker table. Specifically, when inputting worker code “S005”, worker name “AAA”, job title code “Y004”, job title “team leader”, affiliation code 1 “B001”, affiliation name 1 “Manufacturing 1 Section” Is assumed.

  First, the definition support service unit 105 takes in an item selected by the user from the list 1305 of the data definition window 1308 (step 1601). When the acquired item is specified, the definition support service unit 105 determines whether the selected item has an attribute representing a record (step 1602). Here, since an input of new data is assumed, an empty selection portion 1306 having no representative attribute is selected. Therefore, the definition support service unit 105 obtains a negative result, and displays the property display unit 1307 where the attribute value is entered is blank (step 1603).

  As described above, an attribute value is input to the property display unit 1307 by the user. FIG. 17 shows a screen example of the property display unit 1307 in which the attribute value is input. The input proceeds to the state shown in FIG. 17, and the user confirms the input by confirming the setting button 1304 (step 1604).

  Thereafter, the definition support service unit 105 copies the existing record object for all registered data objects and adds it to the record object list of the table object (step 1605). For example, one of the record objects in FIG. 11 is copied to the last row of the table as shown in FIG. FIG. 19 shows the virtual main table after copying. A record object 1901 is a newly added record object. However, at the time of just copying, the attribute value is the same as that of the copy source.

  Therefore, the definition support service unit 105 collates the attribute name of the property display unit 1701 with the attribute name of the data object in the newly created record object 1901 being referred to, and the attribute of the data object having the same attribute name As the value, the attribute value input to the property display unit 1701 is input (step 1609).

  For example, in the case of FIG. 17, the data “S005” in the row 1702 whose display attribute of the property display unit 1701 is “worker code” is input to the attribute value of the data object 1905 having the same attribute name. This process is repeatedly executed for all attributes displayed on the property display unit 1701. At this point, the new input process to the virtual main table is completed.

  FIG. 19 is a conceptual diagram of the virtual main table completed through the correction process after copying. Compared to FIG. 11, it can be seen that new data is input to the bottom line object.

  Next, the definition support service unit 105 proceeds to a subroutine of Step 1610. In this subroutine, if the data input to the virtual main table 1904 includes the attributes of the sub-table used when the virtual main table 1904 is synthesized, and the data is new, the input contents are reflected in the virtual sub-table. It is a process for making it.

  FIG. 18 shows a detailed processing procedure corresponding to step 1610. In this example, the position code 1 “Y004” and the position name 1 “team leader” in the row 1703 of the new input data correspond to new data not included in the virtual position table 1201. Accordingly, these data are reflected in the virtual sub-table through the processing of step 1610.

  First, the definition support service unit 105 enters an iterative process (step 1801), and refers to a data object having the same attribute name as the attribute information object whose type code is “container type” in the referenced record object 1901. (Step 1802). In this example, the data object 1902 in FIG. 19 corresponds.

Next, the definition support service unit 105 acquires the sub-table name “position” from the attribute name “position 1” of the referenced data object (step 1803).
Thereafter, the definition support service unit 105 acquires attribute name / attribute value pairs for all data objects included in the data object list of the data object 1902 being referred to (step 1804). In the example of FIG. 19, two data objects 1903 correspond to this. That is, “title code: Y004” and “title name: group head” are acquired.

  Thereafter, the definition support service unit 105 further enters an iterative process (step 1805), and refers to the first record object 1203 of the virtual title table 1201 that is a virtual table corresponding to the title table that is the acquired sub-table name ( Step 1806). Next, the definition support service unit 105 determines whether data objects having the same attribute name and attribute value as the acquired attribute name and attribute value exist for all the combinations (step 1807).

  In this example, the combination of the attribute name and attribute value acquired in step 1804 is “title code: Y004” and “title name: team leader”, but the data object having the same attribute name and attribute value as this combination is a record. Neither of the objects 1203 exists. Therefore, the definition support service unit 105 obtains a negative result in step 1807. In the loop processing of step 1805, even if the record objects of the virtual subtable are viewed in order, there is no data object having the same combination of attribute name and attribute value, so the definition support service unit 105 proceeds to step 1808. move on.

  In step 1808, the definition support service unit 105 copies the existing record object (arbitrary) in the virtual postal table 1201 together with all lower data objects, and adds it to the record object list of the table object. That is, one of the record objects in the virtual postal table in FIG. 12 is copied to the last line. A line 2102 in FIG. 21 represents a line added by this copy.

  However, the attribute value immediately after copying is the same as that of the copy source. Here, the combination of the attribute name and the attribute value acquired in step 1804 is “title code: Y004” and “title name: team leader”. Therefore, the definition support service unit 105 sets the attribute value of the data object whose attribute name is “position code” to “Y004” and the attribute name of “position name” among the copied record objects. The attribute value is “team leader”. Then, the definition support service unit 105 sets the attribute value of the data object having other attribute names to NULL (step 1809).

  Thereafter, the definition support service unit 105 repeatedly executes the same processing for a data object having the same attribute name as the attribute name of the attribute information object whose type code is “container type”. When M = 2, there are two combinations of attribute name and attribute value acquired in step 1804, “affiliation code: B011” and “affiliation name: manufacturing section 1”. In this case, it can be seen from the record object 1204 of the virtual affiliation table 1202 that the corresponding virtual affiliation table 1202 includes both data objects having the same combination of attribute name and attribute value. For this reason, the definition support service unit 105 exits the loop process in step 1807 and does not add a new record object to the virtual subtable.

  In this way, the definition support service unit 105 generates a temporary virtual subtable record 2102 that includes only newly input items. When a record that does not contain a primary key is generated, in order to notify that a new input record has not been input, the list 1402 in FIG. Finally, when the user finishes inputting the primary key of the virtual sub-table, the process relating to the data definition ends.

  However, the above processing is necessary only when the attributes of the sub-table combined with the virtual main table are insufficient without a primary key. That is, when all the essential attributes are synthesized in the main table, it is not necessary to perform the definition, and the definition can be performed collectively only by inputting to the virtual main table.

(Ii) Data definition change input Up to this point, the processing procedure for inputting new data has been described so far, but in the following, the case of changing existing data will be described.

  In this example, it is assumed that the first record 1104 of the virtual worker table 1101 is to be changed. The processing flow in this case also follows FIG. First, the user selects 1309 representing the representative attribute of the first record in the list 1305 of the data definition window 1308 (step 1601).

  In this case, a positive result is obtained in step 1602 of FIG. In this case, the definition support service unit 105 displays the contents of the first record 1104 of the virtual worker table 1101 as shown in FIG. 23 (step 1606).

  On this display screen, when the user selects an item to be changed, changes the data, and operates the setting button 1304, the definition support service unit 105 receives the operation input (step 1607).

  Next, the definition support service unit 105 refers to the record object registered in the record object list in the same order as the record selected in the list 1305, that is, the first record object 1104 in FIG. 11 (step 1608). .

  Subsequently, the definition support service unit 105 proceeds to processing in step 1609. Steps 1609 and 1610 are exactly the same as described above. That is, if the attribute name of the changed part is the same as the attribute name of the virtual subtable and it is new, the input contents are reflected in the data to the virtual subtable, and other attribute data in the virtual subtable is input. Will be performed by the user.

(G-3) Processing to reflect the data definition input to the original master table Finally, the processing function to reflect the data definition made for the virtual table to the original master table used to generate the virtual table Will be described. This processing function is also executed by the definition support service unit 105.

  FIG. 20 shows an example of the processing procedure of the definition support service unit 105 when the data of the virtual main table is returned to the main table. Here, a case is considered where the data in the virtual worker table 1904 shown in FIG. 19 is returned to the original worker table 201.

  First, the definition support service unit 105 enters an iterative process (step 2001), and refers to the first record object 1906 in the virtual main table 1904 (step 2002). Next, the definition support service unit 105 enters another iterative process (step 2003), and refers to the first data object 1907 in the data object list included in the record object 1906 (step 2004).

  Here, the definition support service unit 105 determines whether or not the attribute information of the attribute information object having the same attribute name as the attribute name of the data object being referred to is a container type (step 2005). In this example, the attribute name of the data object 1907 being referred to is “worker code”, and the type code of the attribute information object having the same attribute name is “character string type”.

  Therefore, the definition support service unit 105 obtains a negative result in the determination process in step 2005, and sets the value of the first cell in the first row of the worker table 201 that is the main table as the attribute value of the data object 1907 that is referred to. Is changed to “S001” (step 2007).

  When N = 3, the attribute name of the data object 1908 being referred to is “position 1”, and the type code of the attribute information object having the same attribute name is “container type”.

  In this case, the definition support service unit 105 obtains an affirmative result in the determination process in step 2005 and refers to the data object 1909 in the data object list of the data object 1908 being referred to (step 2006). It is assumed that the main key of the post table 202 is only the post code.

Next, the definition support service unit 105 is the attribute value of the data object 1909 referring to the value of the cell that comes after the input in the processing in this flow in the first row of the spreadsheet of the main table. Y001 "(step 2007).
Thereafter, a series of processing is repeatedly executed. Thereby, all the data can be returned from the virtual main table to the spreadsheet (that is, the master table).

  Next, the processing procedure of the definition support service unit 105 when returning the data of the virtual subtable to the subtable will be described. However, the processing procedure executed in the definition support service unit 105 is almost the same as that in FIG. Specifically, steps 2001 → 2002 → 2003 → 2004 → 2007 in which all the terms “main” in FIG.

(H) Summary As described above, in the case of the data definition support system according to the embodiment, a virtual table is generated by synthesizing attributes having high relevance among attributes belonging to a plurality of master tables.

  Thereafter, the data definition support system according to the embodiment generates a user interface screen corresponding to the virtual table and presents it as a user work screen. For example, even when functions and parameters are managed as separate master tables, the user is presented with a work screen including both functions and parameters as attributes. By presenting this work screen, the user can proceed with the data definition work while confirming all relationships between functions and parameters that are highly related to each other in the same work screen.

  Incidentally, in the case of the conventional method disclosed in Patent Document 1, data definition work can be executed only in units of master tables, and only the relationship of functions as seen from one parameter can be understood. On the other hand, in the case of the data definition support system according to this embodiment, the data definition operation can proceed while confirming a plurality of parameters related to the function on the screen.

  In the case of the data definition support system according to this embodiment, related data definition operations can be performed all at once. On the other hand, in the case of Patent Document 1, it is necessary to define data while considering the reference relationship for a plurality of master tables. However, the burden of such work is great for a user who is not familiar with the data reference relationship on the relational database (for example, a field worker), and there is a possibility that the definition of data may be omitted. In this respect, the data definition support system according to this embodiment is superior to the conventional method.

  In addition, even when the data definition work is performed while confirming specifications etc. in which necessary information such as piping system wiring diagrams is comprehensively described, the method using the user interface screen according to this embodiment is as follows: You can enter all the data you want to register in the same screen. As described above, the method using the user interface screen according to the embodiment has high consistency with the actual input work, and can be expected to improve usability for the user.

  In the data definition support system according to this embodiment, the input result for the user interface screen can be automatically reflected in one or a plurality of virtual tables related to the user interface screen. Moreover, each piece of data in the updated virtual table is reflected in the corresponding data in the master table used for the synthesis of the virtual table by the data definition support system according to the embodiment.

  As described above, in the case of the data definition support system according to this embodiment, the work necessary for the definition of data can be executed without excess or deficiency without making the user aware of the structure of the master table. Also, the definition result can be reflected in a plurality of related master tables.

(2) Example 2
Next, a second example of the data definition support system will be described.
In the case of the first embodiment, the case has been described where the number of combinations described in the setting file 301 in FIG. 3 is the same as the number of attributes associated with other tables in the main table that is the combination source.

  For example, for the composition number 1 in the sixth line of the setting file 301, there is one attribute “position code” associated with the position table in the worker table. Further, for example, for the composite number 2 on the 10th line of the setting file 301, there are two attributes “affiliation code” associated with the affiliation table, “affiliation code 1” and “affiliation code 2” in the worker table. . In any case, the composite number matches the number of attributes in the main table.

  However, there may be a case where an arbitrary number of records in another master table are linked to one record in a certain master table. For example, it is conceivable that N parameter table records as sub-tables are linked to the attribute of one function table of the main table.

In this case, the same processing is performed until the loop of step 504 in FIG. 5 is exited, and then the following steps are added.
<Step 1>
The composite number of sub-tables described in the setting file is compared with the number of attribute information objects having the sub-table name + number among the created attribute information objects.

<Step 2>
If the composite number is larger, copy the attribute information object having “name of corresponding subtable + number” as the attribute name to be the same as the composite number, Change the number. If it is equal to or less than the number of composites, the process proceeds to step 511 in FIG.

<Step 3>
The order is rearranged in the attribute information object list so that the attribute information objects having the sub table name + number are arranged adjacent to each other in the numerical order.

<Step 4>
Thereafter, the process again proceeds to step 511 in FIG. 5, and the processing after step 511 is executed.

  As an example, let us consider a case where the number of synthesis in the sixth line of the setting file 301 is 3. In this case, the data conversion unit 104 generates a virtual main table 701 being generated as illustrated in FIG. 7 through the virtual table generation process illustrated in FIG.

  As described above, the composite number of the sixth line of the setting file 301 is 3, and the number of attribute information objects having “title + number” is 1, as can be seen from the reference numeral 702 (step 1). .

  Therefore, the data conversion unit 104 copies two attribute information objects having “title 1” as the remaining two attribute names so as to be equal to the composite number, and changes the number of the names so as to be consecutive numbers. That is, the attribute names are “position 2” and “position 3” (step 2).

Thereafter, the data conversion unit 104 rearranges the order in the attribute information object list so that the two attribute information objects that have been generated are adjacently arranged in numerical order (step 3).
Thereafter, the processing subsequent to step 511 is executed again (step 4).

  FIG. 22 shows the process of step 3. An attribute information object having “title 1” as an attribute name is copied, and an attribute information object 2201 with attribute name numbers 2 and 3 is inserted at a position indicated by an arrow in the figure.

When returning data, the following steps are inserted between step 2005 and step 2006 in FIG.
<Step>
If the attribute name “table name + number” of the attribute information object is referred to and the number is larger than the number of attribute blocks of the main table to be linked with the sub-table corresponding to the table name, the step after step 2003 is performed. Move on to the loop.
The processing procedures of all other processes need not be changed.

DESCRIPTION OF SYMBOLS 101 ... Computer 102 ... Data definition support software 103 ... Spreadsheet 104 ... Data conversion part 105 ... Definition support service part 106 ... Setting file 107 ... Virtual table 108 ... Input screen 109 ... Mouse 110 ... Keyboard 111 ... User 201 ... Worker Table 202 ... Position table 203 ... Affiliation table 204 ... Table information display part 205 ... Table data part 301 ... Setting file 401 ... Table object 402 ... Record object 403 ... Data object list 404 ... Data object 405 ... Data object 406 ... Record Object list 407 ... Attribute information object list 408 ... Attribute information object 409 ... Data object list 701 ... Virtual main table 702 ... Attribute information object -Object 901 ... function selection screen 902 ... option 903 ... determination button 1000 ... data object list 1001 ... data object 1
1002 ... Data object 2
1003 ... Data object 3
1101 ... Virtual worker table 1102 ... Two data objects 1103 ... Six data objects 1201 ... Virtual title table 1202 ... Virtual affiliation table 1203 ... First record object 1204 ... First record object 1301 ... Virtual main table Corresponding tab 1302 ... Tab 1303 corresponding to the virtual sub-table ... Delete button 1304 ... Setting button 1305 ... List 1306 ... Empty selection part 1307 ... Property display part 1308 ... Data definition window 1401 ... Tab 1402 corresponding to the virtual post table ... List 1403 ... Property display section 1701 ... Property display section 1702 ... Line 1703 whose display attribute is "worker code" ... Line 1901 whose display attribute is "position code 1" and line 1901 whose display attribute is "position name 1" Newly added record object 1902 ... Data object 1903 in newly added record object ... Two data objects 1904 ... Virtual worker table 1905 ... Data object 1906 with attribute name "worker code" ... first record The first data object 1908 in the data object list possessed by the object 1907... 1906... The data object 2101 in the data object list possessed by the data object 1909. Temporary virtual subtable record containing

Claims (10)

A first means for storing a setting file describing a synthesis rule for a plurality of master tables defining a relational database;
Second means for generating a virtual table in which at least one attribute of a certain master table and at least one attribute of another master table are combined on one table based on a combining rule described in the setting file; ,
A third means for registering existing data managed by the plurality of master tables in the generated virtual table;
A fourth means for displaying the data of the virtual table on a screen;
Fifth means for receiving data input to the virtual table;
A sixth means for registering a result of data input to the virtual table in the virtual table;
And a seventh means for reflecting the data registered in the virtual table in the corresponding data of one or more master tables having an attribute corresponding to the new data. The fifth means includes
A first display field for displaying a list of attributes representing the records of the virtual table;
2. The data according to claim 1, wherein data input is received through a user interface screen having a second display field for displaying an attribute value associated with one record selected in the first display field. Definition support system. The description of the composition rule is
Name of multiple master tables to be combined,
The data definition support system according to claim 1, further comprising: a name of an attribute to be synthesized among attributes of each master table. The data definition support system according to any one of claims 1 to 3, wherein the virtual table has a table structure in which an arbitrary number of attributes belonging to the same master table are combined. When there are multiple collections of attributes belonging to the same master table for data input to the virtual table,
The data definition support system according to any one of claims 1 to 4, wherein the seventh means registers in a corresponding master table as a separate record for each collection of the attribute. A virtual table in which at least one attribute of a master table and at least one attribute of another master table are combined on one table based on a setting file that describes a combining rule for a plurality of master tables that define a relational database A first process for generating
A second process of registering existing data managed by the plurality of master tables in the generated virtual table;
A third process for displaying the data of the virtual table on a screen;
A fourth process for receiving data input to the virtual table;
A fifth process of registering new data input to the virtual table in the virtual table;
And a sixth process for reflecting the new data registered in the virtual table in the corresponding data of one or more master tables having an attribute corresponding to the new data. The fourth process includes
A first display field for displaying a list of attributes representing the records of the virtual table;
The data input according to claim 6, wherein data input is received through a user interface screen having a second display field for displaying an attribute value associated with one record selected in the first display field. Definition support method. The description of the composition rule is
Name of multiple master tables to be combined,
The data definition support method according to claim 6 or 7, further comprising: a name of an attribute to be synthesized among attributes of each master table. The data definition support method according to any one of claims 6 to 8, wherein the virtual table has a table structure in which an arbitrary number of attribute collections belonging to the same master table are combined. When there are multiple collections of attributes belonging to the same master table for data input to the virtual table,
The data definition support method according to any one of claims 6 to 9, wherein the sixth process is registered in a corresponding master table as a separate record for each collection of the attribute.

Images