WO2002091271A1

WO2002091271A1 - A method of creating new relations and categories in a relational database structure

Info

Publication number: WO2002091271A1
Application number: PCT/NO2001/000182
Authority: WO
Inventors: Terje Halvorsen; Jan Henrik Abelvik; Erik Eggen; Gülay KUTAL
Original assignee: Egroup Eon As
Priority date: 2001-05-03
Filing date: 2001-05-03
Publication date: 2002-11-14

Abstract

A method of creating new relations and categories in a relational database structure, in which relations and categories being created in the data base structure without making changes through data definition language commands, as for relations it is defined which objects can be connected to each other, the user thereby defining the meaning of the relation, as to categories, a user can create new categories, define the new category's place in a hierarchy and specify the object types that can be classified under that category, thereafter allowing all users to utilize these relations and categories.

Description

A method of creating new relations and categories in a relational database structure

The present invention is related to a method of creating new relations and categories in a relational database structure.

In a relational database, data entities (tables) are related to other data entities through foreign keys. A foreign key in a table with many columns and rows, may be primary key a) in another big table with many columns and rows, or b) in another small table where the table consists of a few rows and at most two columns: one column for the primary key and another column for the description. Here (a) often implies a logical relation of one kind or another, whilst key (b) often means categorizing. Traditionally both (a) and (b) are implemented in the same way, i.e. through foreign keys. One does not even think that there is a difference between these two. In the following (a) will be denoted as "object relations" and (b) will be denoted as "categories".

Creating a relation or a new category requires changes in the data structure in a relational database. To create a new relation one has to create a new column in the table; to create and use a new category, a "small" table has to be created for the cate- gory itself and then one has to create the new column. In both cases the new column should be defined as foreign key.

In other words, a new column and a new table must be created each time a new category or new categorization is needed. Each time a relation to an existing object is needed, a new column must be created. To create new columns and tables are typically DBA (Database Administrator) tasks. Implementing logical changes that arise from the changes in the data structure, are typical programming tasks. The end user cannot do any one of these operations. Therefore creating new relations and categories are as to now and will stay being impossible for an ordinary user. This fact limits the end user's contribution to the functionality of the application. Good ideas and comments do not come from people who could otherwise have contributed if things were easier to implement. Since new categories are difficult to create, one is forced to think within the existing categories. Categories are sometimes also "sacrificed" in the sense that they start to be used to mean something else than they actually were intended to mean. Another problem with regard to both relations and categories arises when one needs one-to-many or many-to-many relation between entities. These are usually difficult to implement in an application and it is usually difficult to find a good user interface to show them with. The above problems are avoided with the present invention which is related to a method of creating new relations and categories in a relational database structure as defined by the features stated in the claims.

The drawing discloses in figure 1 a data model for customers, vendors and items with their respective categories, figure 2 discloses the data model according to the present invention that replaces the data model disclosed in figure 1, figure 3 discloses the knowledge index, i.e. the user interface, figure 4 discloses a data model for relations between item, customer and vendor tables, figure 5 discloses the data model in the present invention that replaces the data model disclosed in figure 4, figure 6 dis- closes the categories and figure 7 discloses the object relations in the program according to the present invention.

According to the present invention new relations and new categories are created "under run time", i.e. without needing to make database changes through DDL (Data Definition Language) commands. Those who have "knowledge administrator" privileges can define relations and categories. A knowledge administrator is definitely not the same as a database administrator. S/he is the end users' spokesman in organizing the knowledge in a company.

In the case of relations, the knowledge administrator defines which objects can be connected to each other and s/he defines the meaning of the relation. In the case of categories, s/he can create new categories, define the new category's place in a hierarchy and specify the object types that can be classified under that category. Afterwards all users can use these relations and categories.

The following example is used in the rest of the present document. A company that sells PCs, printers and other kind of computer products must have at least three main entities in its data system(s): articles, customers and vendors. These tables contain different attributes like name, address, description and so on. We will not focus on these "simple" attributes; we shall rather focus on foreign keys.

The company buys articles from several vendors and sell them to its customers. The company wishes to categorize its customers as N (Normal), I (Important) and V (Very Important). They also want to categorize their customers according to their business field, such as oil, transport, education and so on.

The company wish to categorize their vendors also as national or international and then according to their country of origin. Precision of delivery is another criteria that they watvt to categorize their vendors with. Items are categorized as PC, server, printer, scanner, RAM chips and so on.

Both items and vendors are also to be categorized according to business fields. After normalization this structure is usually implemented according to the data model disclosed in figure 1. There is an object table in the inventive program that includes reference to different rows in different tables, in this case to the rows in the customer, item and vendor tables. The five tables that are used to categorization are not needed in the program. Values in these tables become rows in one table that contains all categories (or key- words as they are called) instead. The data model becomes therefore less complex as shown in figure 2.

With the method according to the present invention one does not need to create a table for each category. Number of tables in this small example is 5, but in a normal database there may be hundreds of such category tables. Furthermore the data model becomes much less complex.

Putting a new row in an existing table, not creating a whole new table, is enough to create a new category. A new category can be created with DML (Data Manipulation Language) and the INSERT command. Creating a new category in the traditional way demands however a new table, and this is done with DDL (Data Defini- tion Language) and the CREATE TABLE command. Users run DML commands, while the privilege of running DDL commands is often given to Database Administrators (DBA). It therefore provides "power to the people" as power to contribute to structuring the knowledge in their company, without giving them DBA privileges in the database. If the object is going to have several codes within the same category, for example if one will be able to categorize a customer with two or three different business codes, the situation gets even more complex in a traditional implementation. Because even a new table would not be enough in this case, one would need to create yet another table to contain the link between the customer and the business codes. In other words, many-to-many relation between the object and the category necessitates even more database administration and data storage. Since the structure always considers the existence of many-to-many relations, this situation would not demand any extra action.

Additionally, categories themselves can be categorized in a hierarchical structure. This is illustrated as a recursive relationship between the category and itself in figure 2. This structure is called Knowledge Index since it is an index to knowledge. By navigating up or down in this hierarchy of keywords, one reaches at last to the information one seeks. A typical Knowledge Index with keywords and objects looks like in figure 3. When one points at a category, one can choose between seeing objects that are categorized exactly under this category, or objects that are categorized under the sub- categories of that category as well. Different object types can be categorized under different keywords. Which object types are to be categorized under which keywords can be configured by the user. An object can naturally be found on different "places" in the Knowledge Index. An object can belong to several main keywords within a category in several combinations. This is called object's profile, and a profile gives most valuable information on an object. For example using the keyword tree for the organization topic as seen in figure 3 and assigning employees to these keywords, give the opportunity of assigning many profiles to employees. A person can then have the following profiles: "Sales director in the Sales department" and "Vice President". Another person can have the profiles: "Office manager in the Administration department", "Sales consultant in the Sales department" and "Purchase consultant in the Purchase department" at the same time. Similarly an item can have different profiles depending on the way the top-level categories and underlying keywords are designed in the Knowledge Index.

An object is a concrete unit of information; an object type is the abstraction of that unit. In terms of object orientation, an object is an instance of the class object type. In the data structure of our example company, we would have item, customer and vendor as object types, but a specific PC model as an object.

Objects and object types are almost always related to another object or another object type. As we have mentioned earlier, relations are usually maintained through a foreign key that points to a primary key in the other table, or through dedicated tables that manage many-to-many relations between objects.

Items and vendors in the example company are related to each other in the following ways:

1. An item is normally ordered from a specific vendor. 2. If this vendor can't deliver the item, the item can be ordered from 3 other specific vendors.

3. In case of emergency, the item can be ordered from one specific vendor.

4. A vendor can be a "never-order-from!" vendor for a particular item.

5. There is one main vendor for each item. 6. If the item is defect, it is supposed to be returned to a specific vendor (can be the vendor the item is ordered from, or it can be another vendor).

7. The item Y2K compatibility is to be questioned from a particular vendor.

8. The vendor having the highest delivery precision per item is to be specified.

This list can be longer or shorter to include more or less relations between items and vendors, and it can definitely vary from company to company. Eveiy such relation should be defined as a dedicated column and as a foreign key in the item table in a traditional RDBMS (Relational Database Management System). These are specified as rows of a general "Context type" table. The above list can then be rewritten as follows:

1. Object type vendor can have the "shall-be-ordered-from" role for object type item.

2. Object type vendor can have the "can-be-ordered-from" role for object type s item.

3. Object type vendor can have the "order-from-in-case-of-emergency" role for object type item.

4. Object type vendor can have the "never-order-fronT role for object type item.

5. Object type vendor can have the "main vendor" role object type item. o 6. Object type vendor can have the "return-to-vendor" role for object type item.

7. Object type vendor can have the "Y2K contact" role for object type item.

8. Object type vendor can have the "most precise vendor" role for object type item.

Assuming that the company in addition wishes to relate its: s 1. items and customers in 6 different ways, and

2. vendors and customers in 3 different ways, in addition to the 8 relations between items and vendors that are listed above.

The data model in figure 4 shows these relations. The same structure is implemented in the following way in the inventive structure as disclosed in figure 5. 0 Concluding the following advantages are reached with the inventive structure's object relation method:

1. You do not need to create 20 columns; you just put 20 rows into one table. Number of columns in this small example is only 20, but in a normal database hundreds of such relations may be needed! 5 2. The data model becomes much less complex.

3. Putting a new row in an existing table, not creating a whole new column, is enough to create a new relation. A new relation can be created with the DML (Data Manipulation Language) and the INSERT command. Creating a new relation in the traditional way demands however a new column, and this is done

30 with DDL (Data Definition Language) and the CREATE TABLE command.

Users run DML commands, while the privilege of running DDL commands is often given to Database Administrators (DBA). The invention provides "power to the people"; power to contribute to structuring the knowledge in their company - without giving them DBA privileges in the database'.

35 4. If the object is going to be related to another object in an unlimited number of ways within the same context, for example if an item will be able to be ordered from several vendors, the situation gets even more complex in a traditional implementation. Because even a new column would not be enough in this case, one would need to create yet another table to contain the links. In other words, many-to-many relation between an object and another object in this case necessitates even more database administration and data storage. Since the inventive structure always considers the existence of many-to-many relations, this situation would not demand any extra action. 5. Additionally, relations are recursive: one object can be related to a third (or forth or fifth...) object through the second object and through the relation between itself and the second object and the relation between the second and the third object. This is illustrated as a recursive relationship between the object context and itself in Figure 5. Recursive relations give many exciting opportu- nities. Let's say that we define two relations: a) between the person and the document object: "author" b) between the document and the process object: "vision".

This gives a recursive relation between a person and a process through a document. With the help of three rows in the object context table, it can be concluded that: "Person Al has written (been the author of) the vision document Dl in the process PI."

The methods according to the present invention are a unique way to think. With its categorising and object relations, it makes complex data structures much easier, more optimal and more dynamic. It gives "power to the people": power to contrib- ute to structuring the knowledge in their company. It is a framework that can be shaped in many different ways. It is an organization's key to success in managing the knowledge.

Claims

P a t e nt C l a i m s

A method of creating new relations and categories in a relational database structure, characterized in relations and categories being created in the data base structure without making changes through data definition language commands, as for relations it is defined which objects can be connected to each other, the user thereby defining the meaning of the relation, as to categories, a user can create new categories, define the new category's place in a hierarchy and specify the object types that can be classified under that category, thereafter allowing all users to utilize these relations and categories.

2. Method according to the preceding claim, characterized in an object table including reference to different rows in different tables, such as to the rows in the customer, item and vendor tables and one common table, without needing to use many small category tables for categorization as values in such tables being rows in one table that contains all categories.

3. Method according to the preceding claims, characterized in an object belong to several main keywords within a category in several combinations as defining the profile of the object, said profile providing most valuable information on an object, such as using specific keywords for example for an organization topic, assigning for example specific users to the combination of these keyword.