JP3655177B2 - Product information database system - Google Patents

Description

[0001]
BACKGROUND OF THE INVENTION
The present invention relates to a product information database system. In particular, the present invention relates to a product information database system that integrates and manages a wide variety of product information with a generalized schema.
[0002]
[Prior art]
The product information database system is a database system that searches for the price, size, weight, features, external dimensions, appearance image, and the like of the product from the product name and product model number, for example. Such a merchandise information database system is used by a manufacturer's distributor, etc. as an alternative to the conventional booklet-type paper product catalog. Alternatively, a manufacturer / distributor or catalog production company may build such a database in order to produce a catalog of booklet-type paper.
[0003]
In general, the product information database consisting of various product groups is divided into product groups that can be described by the same set of items, just like conventional paper product catalogs list products by product genre. A product specification table is defined for each product group, and information is recorded and managed.
[0004]
[Problems to be solved by the invention]
Product information is managed by separate product specification tables for each product group. In the product information database so far, input form for registering data, search program, ledger output program for each product, etc. There was a problem that it was necessary to create a design every time, and it took time to develop the database.
[0005]
Also, changing the definition of a product group, that is, increasing the number of items describing a certain product group, or dividing what was handled as a single product group, requires a change in the definition of the product specification table. Since this is not desirable in terms of database system maintenance, there is a tendency for database designers to force complete database design that does not require changes to the product specification table in the future. This caused the database designers to be careful and increased the time to database operation. If the definition of the product specification table is unavoidably changed after the database is in operation, database maintenance costs were required.
[0006]
The present invention has been made in consideration of such problems, and provides a product information database system that can be managed centrally with a generalized schema even for product groups with different product information description items. This is the issue. It is another object of the present invention to provide a product information database system that can collectively manage information representing combination products with a generalized schema.
[0007]
[Means for Solving the Problems]
In order to solve the above-mentioned problem, the first invention defines a product structure that is a combination of item names for each product group described by a combination of the same items, and each product structure of a plurality of product groups A product structure table is created, and the actual product data includes three items: 1) a product number, 2) a number that identifies an item name or item, and 3) its value, regardless of the product structure. A product information database system for managing a plurality of types of product information characterized by being able to handle a wide variety of product information in a unified manner by recording and managing a set in a product specification table with one record data ,
Information describing a combination product composed of a combination of a plurality of products includes 1) the product number of the combination product, and 2) A number that identifies the positioning of one component product that constitutes the combination product in the combination product And 3) recording and managing as a related product link table, which is a table having the same configuration as the product specification table, including a set of 3 product numbers of the product itself in one record data. The gist is to do.
[0008]
As a preferable aspect of the first invention, in the product information database system according to the first invention, information describing a combination product is:
2) Instead of the number that identifies the positioning of one component product that constitutes the combination product in the combination product, 2) 1) Uniquely identify the category in the combination product to which the one component product that constitutes the combination product belongs Using a number and 2) 2) a number that identifies the positioning of the one component product in the category And a related product link table including a set of four of the 1) the product number of the combination product and the 3) the product number of the component product itself in one record data.
One record data includes a set of three items: a) a product number of a combination product, b) a category constituting the combination product, and c) an identification number for correspondence with a category name of the category. Related product category table,
An item table describing the correspondence between the identification number and the division name for taking the correspondence between the division and the division name
It is good also as a merchandise information database system recorded and managed by these three tables.
[0011]
DETAILED DESCRIPTION OF THE INVENTION
Hereinafter, embodiments of the present invention will be described with reference to the drawings.
[0012]
FIG. 2 is a diagram conceptually showing two product groups stored in one product information database and having different combinations of item names. The product group A corresponds to a reception set, and a product specification is composed of three types of items: product name, list price, and product description text. On the other hand, for the product group B, the product specification is composed of the product name, product number, list price, size, color, and weight.
[0013]
FIG. 3 is a diagram for explaining the prior art. In the conventional typical product information database system, since the combination of items constituting the product specifications is different for each product group A and product group B, each product group has the number of columns corresponding to the number of items. Two-dimensional tables 901 and 902 are defined. As shown in FIG. 3, the number of columns in the table 901 representing the product group A is 4 including the product ID, and 7 in the table 902 representing the product group B. In addition, in practice, a management table 900 indicating which products are described in which tables is necessary for later retrieval.
[0014]
However, in this method, an input form, a search program, a ledger output program, etc. must be developed for each table. Alternatively, as shown in FIG. 9, the management table 900 includes the number of items and a list of items used in the table, and each processing program is devised to provide an input form, search program, In some cases, it is possible to create a ledger output program or the like, but some ingenuity is required in both database design and processing program design.
[0015]
On the other hand, in the product information database system of the present invention, a product structure as shown in FIG. 4 is defined for each product group having a different type. The defined product structure is created and recorded as a product structure table. For product group A, the product structure A in FIG. 4 (items used are product name, price, description), and for product group B (same product name, product number, price, dimensions, color, weight) 4 product structure B is defined. Then, an item management table 820 which is a table in which all items used in those product groups are collected is created.
[0016]
The item management table 820 lists all items for displaying products of all product genres (product groups) published in the item (usually equivalent to one catalog booklet). For one item used, a combination of an item name that is the name of the item and an item type classified by the value type of the item is recorded.
[0017]
Furthermore, FIG. 1 is a diagram illustrating a product specification table 830 that records specification information of individual products. In the product specification table 830, the product number (product ID), the item name, and the value of the item form one set of data, that is, data of one record. With this product specification table 830, specification data of various products with different product structures can be stored in one type of table. That is, data of n rows (n records) is created for products having a product structure described by n items, and k rows (k records) are generated for products having a product structure described by k items. Data may be created and added to the product specification table 830. Since each line (each record) always includes a product ID, the item name and the product corresponding to the value are always associated with each other. In this way, product specification data is recorded in a one-dimensional manner, so in FIG. 1, the specification of the product with product ID 1001 belonging to different product groups and the specification of the product with product ID 2001 are the same. Described on the table. Since the product specification table 830 records the product specification data itself, it can be said to correspond to the tables 901 and 902 of the conventional example. However, compared with the conventional example shown in FIG. 3, the amount of data increases as much as the item name needs to be described in each row (each record). However, the merit of being able to describe all product specifications in a single product specification table is greater than the shortcomings. This will be described later.
[0018]
Since the specification information is correctly stored in the product specification table 830 according to the product specification type of each product, when registering data in the database system of the present invention, the product structure table corresponding to the product is referred to The item name and its value are registered in the product specification table 830 according to the structure described in (1). The product structure table is referred to only when data is registered, and is not referred to when searching.
[0019]
As shown in FIG. 6, the actual product specification table is defined by dividing into product specification tables such as character data, price data, numerical data, and selection data according to the data type of item values. FIG. 6 shows a character table 831 and a price table 832.
[0020]
FIG. 7 is a flowchart for explaining search processing in the product information database of the present invention. The flow of processing in the case of performing “search example 1: search for a product ID such that (item 1) satisfies (condition 1) and (item 2) satisfies (condition 2)” is shown. First, the item management table is referred to and the item type to which (item 1) and (item 2) belong is examined (S10). As a result, when it is found that (Item 1) corresponds to the character table and (Item 2) corresponds to the price table, an SQL statement for inquiring the search example 1 to the database is created (S20). Then, the SQL statement is executed (S30).
[0021]
On the other hand, FIG. 8 is a flowchart showing a search procedure of Search Example 1 in the typical conventional product information database system (hereinafter referred to as conventional DB) shown in FIG. First, with reference to the management table 900, a product group in which both (item 1) and (item 2) are used is examined (S110). For this purpose, the management table 900 actually records the item names used by each product group and the corresponding tables 901, 902,... Simultaneously in the management table, as shown in FIG. Next, an SQL statement for querying the database for search example 1 is created (S120). Then, the SQL statement is executed (S130).
[0022]
The database of the present invention (hereinafter referred to as the present invention DB) is compared with the conventional DB. First, the search process itself is not much different when FIG. 8 and FIG. 7 are compared. However, in step S20 corresponding to the DB of the present invention and step S120 corresponding to the conventional DB, there is a possibility that a considerable difference in required development effort may occur at the design and development stage of the program that creates the logic for generating the respective SQL statements. is there. In S120, it is necessary to know the schema of the target database (as shown in FIG. 3, the types of tables designed for each target product catalog, the configuration of each table) and carefully design them. In S20, the table to be handled is always only a one-dimensional table such as the product specification tables 831, 832,..., So that it is possible to easily design and develop using the logic of other databases developed in the past. Because. Hereinafter, three cases with definition (create) of the product specification table will be compared.
[0023]
(First Case Comparison) First, the difference when it is necessary to increase the product genre is examined. In the conventional DB, two steps of 1) correcting the management table 900 and adding one record corresponding to a new product genre and 2) creating (creating) a new product specification table are necessary. On the other hand, in the present invention DB, only one product structure which is one record data of the product structure table is added.
[0024]
(Second case comparison) Also, what if there is a need to increase the item structure of existing product genres? In the conventional DB, first, 1) the management table 900 (FIG. 9) is corrected, and the item structure of the product genre is corrected. Next, 2) A new product specification table is defined (created). 3) Move all data in the old table to the corresponding item field in the corresponding record in the new table. Finally, 4) delete the old table, and change the correspondence table from the old table to the new table for the record data of the product genre in the management table. The above four steps are necessary. On the other hand, in the present invention DB, it is only necessary to add one product structure which is one record data of the product structure table.
[0025]
(Third case comparison) What if you have to divide what has been treated as one product genre so far into two? It is assumed that the number of items in one divided product genre is small. In the conventional DB, first, 1) a record for a new product genre is added to the management table 900. Next, 2) A new product specification table is defined (created). 3) For each product ID, transfer necessary data records among the necessary product IDs to a new product specification table. Finally, 4) The record data of the product ID moved to the new table is deleted from the original product specification table. The above four steps are necessary. On the other hand, in the present invention DB, it is only necessary to add one product structure which is one record data of the product structure table even in this case.
[0026]
FIG. 10 is a table showing a comparison of the above three cases. The necessary procedure for the conventional DB is shown in the first column, and the necessary procedure for the DB of the present invention is shown in the second column. In the present invention DB, since it is not handled in a separate product specification table for each product group (product genre), since the operation of the product information database has been started, the database schema (as shown in each table) Even if it is necessary to change the definition and the structure of the table, there is no need to move or delete the data recorded in the product specification table. This is the greatest advantage of the DB of the present invention. The product structure (description contents of the product structure management table 810) needs to be changed only for the subsequent registration of product information, and the already registered specification data (specification information table 831). , 832,... Need not be changed at all.
[0027]
However, it should be noted that the description of the above three cases is a merit in the case where the product information database is configured by making the best use of the features of the present invention. That is, in the spec data (contents of the specification information table), the presence of a blank item (product ID that does not have a record indicating the value of the item name defined in the product structure 810), an unnecessary record (not defined in the product structure 810) In this case, the search for the product information and the display output process of the product information are complicated accordingly. In an actual design, a design that does not allow blank items and does not allow unnecessary records may be realistic in implementing search and display output functions. When such a design is selected, in the second case, 1) after changing the related product structure, 2) a record that describes the value of the item name added to the specification information table so as not to create a blank item. Add all at once. It becomes the procedure. In the third case, after 1) newly defining the product structure and adding one, the product group corresponding to the newly defined product structure is 2) unnecessary items to delete unnecessary records. The procedure is to delete the record of the product ID having the name. This procedure is shown in the second column [...] In FIG. Regardless of which design is adopted, it is not necessary to change the structure of the specification information table into which the specification data is stored in the database of the present invention, and it is not necessary to move the already recorded specification data.
[0028]
The basic features and advantages of the product information database system according to the present invention have been described above. Hereinafter, a more practical configuration of the product information database system according to the present invention will be described.
[0029]
FIG. 5 is a diagram for explaining how to manage items handled by the item management table 820. Items used by the product information database system of the present invention for describing product specifications can be classified as specification items. In addition to the classification of items, material items, which are items used to describe the link relationship with other independent material files such as images and charts of the product, and one combination product and multiple related components There is a related product item that hierarchically describes the relationship with the product. Furthermore, the specification items are classified into several types according to data types of values that the items can take, such as character items, numerical items, and price items. The material items are also divided into several types according to the type of material, such as images, charts, moving images, and sounds. The related product item is classified into a related product category which is a higher level and a related product positioning type which is a lower level. As shown in FIG. 11, the structure management table 810 is defined by being divided into a plurality of structure management tables (811, 812, 813) for each item type. As shown in FIG. 11, in the structure management table for each item type, items for describing specifications of a certain product group and describing link of material data are illustrated in FIG. 5 instead of item names. It is described by the item number assigned according to the numbering rule for each item type. FIG. 12 shows the contents of the item management table 820. The correspondence between the item number and the item name is described only in the item management table in this way. Therefore, in an actual database configuration, the item number defined in the item management table 820 is described in the item name column of each record (each row) of the product specification tables 831 and 832.
[0030]
FIG. 13 is an explanatory diagram showing how to manage material data. In the product information database system of the present invention, product specification information and material information are managed by independent table groups. The link table of FIG. 13 is a table corresponding to the product specification table 830 and is prepared for each material such as an image material and a chart material. The image material link table 891 represents the link relationship between the image materials, and the chart material link table 892 represents the link relationship between the diagram materials. The item name is actually the item number. As can be seen, like the product specification table 830, it has a one-dimensional structure. At the time of registration, the structure management table 810 divided by item type for each material such as image material and chart material is referred to. Therefore, the correction necessary when the structure of the material data defined for each product group needs to be changed does not allow the structure management table 810 to be corrected and added, and blank items and unnecessary records in the material link tables 891 and 892 are not allowed. In such a case, only necessary records are added to or deleted from the material link tables 891 and 892 so as to satisfy the condition.
[0031]
FIG. 18 is a diagram for explaining a method for managing and recording information attached to material data in the database system of the present invention. The material data with the material ID 101 is recorded with information about the material data itself and comment information added to the material. The former is recorded in the material information table 700 as shown in FIG. The latter is also recorded in the material additional information table 710. Similarly to the product specification table 830, the material additional information table 710 has a one-dimensional structure, and accordingly, items for a certain material (in FIG. 18, “photograph No.” and “explanation”). Even when the number is increased, there is no need to change the structure of the material additional information table 710.
[0032]
In an actual product catalog, a certain product may be composed of a combination of a plurality of products. For example, as shown in FIG. 15, a certain product (combination product, product ID = 101) may be composed of 10 related products. In the database system of the present invention, the link relationship of such combination products is recorded and managed by two tables, a related product classification table 600 and a related product link table 630 shown in FIG. The reason why the two tables are used is that, as shown in FIG. 15, the relationship is described in three layers of combination product-category-positioning (related product) (the positioning corresponds to the related product on a one-to-one basis). To do). The related product category table 600 represents the correspondence between the combination product and the category corresponding to one group obtained by dividing the related product into several groups. The related product link table 630 represents the correspondence between the combination product, the category, and the related product. By referring to the related product link table 630, all the related product IDs of the combined products can be specified.
[0033]
Similarly to the product specification table 830, the related product classification table 600 and the related product link table 630 have a one-dimensional structure, and accordingly, when it is necessary to increase the related products of a certain combination product, the related products It is only necessary to add one record to the link table 630, and there is no need to change the table structure. For example, in FIG. 15, when adding a related product 9004 belonging to a new category 1, one record with (product ID = 101, div_seq = 1, part_seq = 4, related product ID = 9004) is set as a related product link table. Just add it to the end of 630. Alternatively, when it is desired to add the related product 9004 as belonging to the category 4, a record (product ID = 101, div_seq = 4) is added to the end of the related product category table 600, and (product ID = 101, div_seq = 4, part_seq = 1, related product ID = 9004) is added to the end of the related product link table 630. The item of the item table ID in these two tables means an identification number for taking correspondence between the classification and position and the name (item name) given to them. This correspondence can be taken in the item table 620 of FIG. If defined in this way, it is possible to search for related products by using the item name assigned to the category or position as a key. In addition, the hierarchy called a division can also be omitted (in this case, the table 600 and Table 630 Div_seq column is not necessary), but it may be inconvenient if it cannot be searched by category (name given to).
[0034]
In an actual product catalog, there is a case where it is desired to relate common information (display of a specific brand or display of common product description text) to a plurality of products. In this case, the range of products related to the common information is usually a range smaller than the defined product group, but there are cases where the product range spans a plurality of product groups. FIG. 14 is a diagram for explaining the relationship between the main table 800 and the product specification table 830 used in such a case. The main table 800 associates common information with each product ID. The common information also has the common information ID number. The common information itself has a common information structure that is a combination of item names, and the content itself is described in the product specification table as well as the value of the item of the product specification, as shown in FIG. Actually, the content of the character type item of the common information of a certain common information number is described in the character table 831.
[0035]
【The invention's effect】
As described above in detail, in the database system of the present invention, the product specification data is recorded in a single table for each data type regardless of the product genre. Even if you have to change it, you don't have to worry about database management. The same applies to material data and information representing combination products. Therefore, it is possible to maintain the consistency of the product information database system at a low cost in the future. Further, when designing a large-scale product database system, schema design can be easily performed, so that the database designer is not burdened and the time from the start of design to the start of operation can be reduced.
[Brief description of the drawings]
FIG. 1 is an explanatory diagram of a product specification table 830;
FIG. 2 is a diagram illustrating a product group with different item sets constituting specification information.
FIG. 3 is a diagram illustrating a conventional technique.
FIG. 4 is a diagram illustrating a product structure definition.
FIG. 5 is a table for explaining details of item management;
FIG. 6 is an explanatory diagram of an actual product specification table.
FIG. 7 is a flowchart for explaining a search processing procedure of the product information database system according to the present invention.
FIG. 8 is a flowchart for explaining a search processing procedure of a conventional product information database system.
FIG. 9 is a diagram showing a management table of a conventional product information database system.
FIG. 10 is a comparison table between the product information database system of the present invention and a conventional database system.
FIG. 11 is a diagram for explaining an actual structure management table;
FIG. 12 is a diagram for explaining an actual item management table;
FIG. 13 is a diagram for explaining how to manage materials;
FIG. 14 is a diagram for explaining how to write common information.
FIG. 15 is a diagram illustrating an example of a parent-child relationship link of a product.
FIG. 16 is an explanatory diagram of a table configuration expressing links of related products.
FIG. 17 is an item table related to links of related products.
FIG. 18 is an explanatory diagram of a table describing material additional information.
[Explanation of symbols]
600 Related Product Classification Table
620 Related Product Item Management Table
630 Related Products Link Table
700 Material information table
710 Material additional information table
800 main table
810 Product structure management table
811 Product structure management table (for name item)
812 Product structure management table (for price items)
813 Product structure management table (for character items)
820 Item management table
830 Product specification table
831 Product specification table (for character data)
832 Product specification table (for price data)
891 Image material link table
892 Diagram material link table
900 Management Table in Prior Art Product Information Database
901 Specification table for product group A in the product information database of the prior art
902 Specification table for product group B in the product information database of the prior art

Claims (2)

For each product group described with the same item combination, define a product structure that is a combination of item names, create a product structure table that describes each product structure of multiple product groups, Regardless of the product structure, the data is recorded and managed in a product specification table in which a set of 3) 1) product number, 2) item name or item identification number, and 3) its value is set as one record data. A product information database system for managing a plurality of types of product information characterized by being able to handle a wide variety of product information in an integrated manner,
Information that describes a combination product composed of a combination of a plurality of products, 1) a product number of the combination product, and 2) a number that identifies the position of one component product that constitutes the combination product in the combination product , 3) A product which is recorded and managed as a related product link table, which is a table having the same configuration as the product specification table, including one set of three product numbers of the component product itself in one record data. Information database system. In the product information database system according to claim 1, information describing a combination product is:
2) Instead of a number that identifies the positioning of one component product constituting the combination product in the combination product, 2) 1) Uniquely classifies the combination product to which the one component product constituting the combination product belongs And 2) 2) the number specifying the positioning of the one component product in the section , these, 1) the product number of the combination product, and 3) the product of the component product itself A related product link table including a set of four numbers in one record data;
One record data includes a set of three items: a) a product number of a combination product, b) a category constituting the combination product, and c) an identification number for correspondence with a category name of the category. Related product category table,
A merchandise information database system characterized by recording and managing three tables of item tables describing the correspondence between the identification numbers and the division names that correspond to the divisions and the division names.

Images