Dr. Paul Dorsey

Dulcian, Inc.

Overview

Building applications to support multiple user groups entails discussion of more than one topic. There are two areas to worry about. From the application side, you must make them look and behave differently depending upon the group of users. From a data perspective, you have multiple users all accessing the same database and you want the way in which they perceive the data to be governed by what data they are allowed to see.  At its most extreme, we can have two organizations storing their information in the same database and using the same applications. However, neither group of users is ever aware that the other group exists. Moreover, even though they are using the same applications, the applications themselves may look and behave very differently.

Creating a system to support multiple user groups is a very complex and difficult problem. As in any difficult design process, you should use the best talent available and carefully articulate the goals of the project and go through a few iterations. The same level of care applied to supporting multiple user groups is needed as when setting up a security plan for a system to support a financial institution. If possible, the first time these types of requirements will be supported, you should keep your requirements as modest as possible. One of the surprising aspects of this problem is that in attempting to support the ambitious goal of multiple customers sharing the same database unaware of each other, you will end up building as much complexity into the system as is needed to support the rest of the business requirements combined.

Difficulties Using Existing Solutions

To illustrate the difficulty of supporting different user groups, one need only examine existing solutions to pieces of the problem. On the application side, Oracle has a product called Translation Builder where translations of all textual information (not data) in forms were entered. The translation builder created different versions of the .fmb files for different users. This is not an optimal solution. Using this method, any time a form is created, an extra compilation process is necessary and multiple versions of every form must be maintained. To support five languages would require five times as many forms to prepare for deployment. If you only deploy the French form in France and German forms in Germany, if a German user happens to be working in France, he/she wouldn’t be able to see the application in his/her native language.

Anyone who has used any of the popular ERP packages and tried to customize them is familiar with the complexity and difficulties associated with using the so-called “flex fields.”

Detailing all of the different aspects of this problem and its solutions would easily fill a book. This paper will present an overview of a few possible situations where different user groups can be supported.

Describing the User Groups

The first task to be accomplished is to determine how to describe the user groups at the outset. If you are using a generic “Party” structure (meaning that all customers, employees and contacts are placed into the same table or set of tables), they your user groups are simply another type of party.

Using a more traditional (less generic) structure with separate employees, vendors, customers, etc., you will probably need to create an additional table called USER_GROUP, which can be attached to the relevant structures in the system.

Multiple User Group Examples

There are several types of multiple user groups that might be common to many systems:

1.       Users speaking different languages

2.       Users employing different currencies

Examples of each of these will be discussed.

1.     Multi-lingual Label Support

Supporting customer preferences for text labels in Oracle Forms has a fairly straightforward solution as shown in Figure 1.

 

 

 

 

 

 

If you are building systems generically, you can use a data model like the one shown in Figure 2, which has the advantage of supporting any product and not just Oracle Forms.

If you are only interested in keeping a library of terms, you can use the structure shown in Figure 3.

 

 

Figure 3

Once the information is stored in the database, how should you implement it to modify your applications? You can use the Translation Builder approach and physically generate a separate version of your form for each user group using the Forms API. However, for the reasons mentioned above, this is not a desirable strategy.

The alternative is to change your form labels on the fly when the form opens for the first time. Using this strategy, there are two alternatives:

1.       Leave the translated information stored in the database and retrieve it.

2.       Use the information in the database to generate a library attached to your form.

If you leave the information in the database, you will have to incur the overhead of querying the database every time that the form is opened. Generating the PL/SQL library requires a bit more effort; but once the library is compiled and attached to the forms, it would not require the query every time the form opens. The speed impact in bringing back a few dozen labels to your form is probably negligible. Unless you are concerned about carefully minimizing network traffic, the extra effort is not worthwhile.

2. Multi-lingual Data Support

Handling multi-lingual values in primary data tables is very difficult. This cannot be supported in a traditional relational database since nested tables would be required to support multi-lingual data effectively. In these nested tables, the value of every translation field is actually a two-column nested table with Value and Language as the two columns. Any other way of handling this will probably not work. The solution is to create the main data table in the primary language of the organization with a translated value child table to store other languages as shown in Figure 4.

In this case, there is a project table with a number of translatable columns. The translated versions of each value are placed into the Project Translated Value table as shown below:

PROJECT
PROJECT_ID_PK	NAME	BUDGET
1	Big Project	10,000
2	Little Project	100

 

 

LANGUAGE

LANGUAGE_NAME

French

German

 

PROJECT TRANSLATED VALUE
TRANSLATED_ID	PROJECT_ID	NAME	LANGUAGE
100	1	Grosse Projekt	German
101	1	Kleine Projekt	German
102	2	Projet Grand	French
103	2	Projet Petit	French

 

This data model and accompanying tables are not perfect. The only time that this data model would be optimal would be if all usage of the system is in a particular language and users were only interested in translated values. This is not realistic for most international systems. If you code the system as described above, you would need one set of applications for English-speaking users and one for non-English speaking users. Even worse, if you wanted to find a particular project by name, you would need to look in different places depending upon the selected language.

To solve these problems, you can restructure the tables so that all of the translated values are in the translated values table as shown below:

 

PROJECT
PROJECT_ID_PK	BUDGET
1	10,000
2	100

 

LANGUAGE

LANGUAGE_NAME

French

German

English

 

PROJECT TRANSLATED VALUE
TRANSLATED_ID	PROJECT_ID	NAME	LANGUAGE
100	1	Grosse Projekt	German
101	1	Kleine Projekt	German
102	2	Projet Grand	French
103	2	Projet Petit	French
104	1	Big Project	English
105	2	Little Project	English

 

Using this structure allows all of the translated values in any language to be stored in the same place. The database is now language-neutral with no one language preferred over another. Making the applications support any language is therefore much easier. What is missing is knowing which value is the original and which are the translated versions for any particular record. There are two ways to handle this problem:

1.       An indicator field can be placed in the table that allows one of the values to be flagged as “original.”

2.       A recursive link can be placed on the Translated Values table. This allows you to link the translated values to the original values through a referential (recursive) pointer.

Either method will work. However, I prefer using the recursive structure of the second approach, which provides additional benefits discussed later in this paper.

3. Multi-currency Support

Multi-currency issues can be handled in a way similar to multi-language issues. There are two alternatives:

1.       Any currency value in a system is time stamped. A currency conversion table is stored separately as shown in the data model and tables below.

 

 

CURRENCY
CURRENCY	NAME
USD	US Dollar
DM	Deutsch Mark
FF	French Francs
ED	Euro Dollar

 

CURRENCY CONVERSION
CURRENCY	START DATE	END DATE	EXCHANGE RATE
USD	1/1/1900	-	1
Euro	1/1/2000	1/7/2000	1.01
Euro	1/1/2000	1/15/2000	.98
DM	2/1/2000	2/8/2000	1.97
FF	2/1/2000	2/15/2000	6.60

 

To find out the value of any transaction converted, the system can find the exchange rate where the value rate is between the start and end date in the currency conversion table. Note that, in this case, we have used US dollars as the reference currency. This is a simple mathematical calculation to convert one currency to another. IN the sample table shown, currency values are updated weekly. Depending upon the system requirements, the currency conversion table may need to be updated daily or even more often, pulling information from the currency exchange. In the table where multi-currency values are stored, you will need to add a date and a currency. For example, with the PROJECT table from the multi-lingual example, the table would be modified as follows:

PROJECT
PROJECT_ID	BUDGET	BUDGET DATE	BUDGET CURRENCY
1	10,000	1/1/2000	USD

 

Note that we have explicitly declared a currency and date for the budget field. If there are multiple currency columns in the table, depending upon the application, you can either use the same date and currency for each one or you may need a separate one for each field based upon how the information is gathered. The most likely scenario is that every currency value in the record would be associated with the same currency but the relevant data for each value would likely be different. Each record would have only one currency, but a separate date for each currency field.

4. Multi-User/Multi-Lingual Reference Table Support

This topic requires more description than is possible to adequately cover in a short paper. For more information, see the Dulcian website (http://www.dulcian.com/) after the conference.

5. Extra Columns for Core Tables

Frequently, a situation arises where a particular group of users want to add one or more attributes to an existing table. This presents significant problems for supporting the system. If each group of users has one or more different additional attributes, these must either be visible to all users or special programmatic control must be added to determine which group sees which attributes.

Traditional COTS vendors have handled this by placing some number of additional unused columns into each table. These are often called “User Defined 1, User Defined 2..” etc. There is nothing inherently wrong with this approach. However, the user extensibility is limited to the number of additional columns added to each table. If you are trying to support multiple groups of users in the same database, this approach will probably not work. The problem is compounded when additional column capability is combined with multi-lingual support.

A more stable approach involves allowing users to define extra columns on the fly and store all values in a child table similar to the multi-lingual support model. This allows any number of user-defined columns to be specified. The data model (Figure 5) and tables to support this are shown next. Note that there are three value columns in the VALUE table, one for each data type. This allows you to perform searches for values by range without performing datatype conversions, which would defeat the indexes.

PROJECT VALUES
PROJECT_ID	VARIABLE_NAME	VALUE_TX	VALUE_NR	VALUE_DT
1	Months_duration		6
1	Date_suspended			01/05/2000

 

It may be possible to store all values in text fields and use Oracle8’s ability to index by a function but we have not yet verified the effectiveness of this technique. The DOMAIN table allows us to define domains that restrict the valid values for a user-defined variable. These valid values can either be defined using the valid value list or queried from elsewhere in the database. The query to bring back the information can similarly be stored as text in the DOMAIN table.

To declare which extra variables are appropriate for a specific user group, an additional association table would be used as shown in Figure 6.

 

We can easily make this structure multi-lingual with the following modification to the data model.

 

 

Now it is clear why the recursive structure was used for additional values. If we had used the indicator column technique to distinguish the original from the translated values, then we would have been unable to allow for an available name to be repeated in a particular table. This is a desirable feature. For example, if you wanted to store all colleges that an employee attended, then, using this structure “COLLEGE ATTENDED” could appear multiple times in the VALUE table while retaining the ability to distinguish its core value from its translated values. Information regarding whether a field is required for a particular user or can appear multiple times should be stored in the VALID VARIABLE table.

 

About the Author

Dr. Paul Dorsey (pdorsey@dulcian.com) is the founder and President of Dulcian, Inc., (http://www.dulcian.com/) an Oracle consulting firm that specializes in data warehousing, web and client-server systems development and products that support the Oracle environment. Paul is co-author with Peter Koletzke of Oracle Press’ Oracle Designer Handbook, Oracle Developer Forms and Reports: Advanced Techniques and Development Standards and with Joseph Hudicka of Oracle Press’ Oracle8 Design Using UML Object Modeling. Paul is an Associate Editor of SELECT Magazine and is President of the NY Oracle Users’ Group.