Subject Matters and Workflow Role in Application Development

By Thomas Eklund

When subject matters and workflow are used incorrectly, the results are never pretty. So, these are the issues that this article addresses.

Both subject matters and workflow are determined based on the information collected during business analysis process. However, for database application's development these two concepts must be used for their own separate purposes.

Subject matters and workflow are not the same and may lead to different conclusions when they are used for application development. So, it is important to apply the two sets of principles to the right parts of application development. Otherwise the result will most likely be an application that is difficult to use: the application will either be difficult and cumbersome to navigate or cannot be developed so that all the possible information entry, modification, querying, displaying and reporting features can be used.

Contents

- Using Workflow
- Using Subject Matters
- Summary

Top

Using Workflow

Workflow related aspects should be used for deciding what kinds of user interfaces are needed (for example, what forms, reports and their elements are needed and how forms should be linked and accessible).

Workflow should be assessed above all from application's usage perspective. From an individual user's perspective workflow means the total experience that the user has and combines content, user interface and application's navigation structure usage experience. Content, navigation tools, selection of buttons and fields that the users can interact with and additional features should be presented so that they allow users to experience a coherent flow of information when they use the application.

So, the information should be grouped or distributed on the user interfaces based on workflow.

However, when user interface is developed by following subject matters instead of workflow, it may seem that it is a good idea to group absolutely all the information regarding, for example, organizations in one place and all the information regarding people in another place so that all the fields regarding particular subject matter are displayed together and the application's users interact with the fields they need and skip the rest.

Similarly, it may also seem like a good idea to focus on trying to fit as much information as possible on each row and on every page.

However, in most instances during specific type of application usage processes application's users interact with particular fields that characterize the subject matter, and then need to interact with another, closely related subject matter. Again, organizations and people can be provided as an example. So, the user interface and options available on the user interface should be based on application users needs and help to produce coherent flow of information for the users.

Top

Using Subject Matters

Principles relevant to working with subject matters should be used for relational database data storage structure development, that is, for development of tables, table fields, field properties and relationships between the tables. There are exceptions to this principle that involve large data archives. These exceptions are beyond the scope of this article.

Examples of subject matters are organizations, people, ingredients, and products. As part of the development process, subject matters become tables and each subject matter's characteristics relevant table fields.

When data storage structure is built by connecting the subject matters that the application deals the resulting structure is both easier to understand and work with. Further, many-to-many relationships can usually be set up so that one of the subject matters tracked will perform the intermediate table role.

Each subject matter (people, organizations, etc) must be in a separate table so that the fields characterize that and only that subject matter. There should be only one main table per subject matter. However, characteristics, or table fields, may draw information from other tables, called lookup tables, in cases where there are several alternatives available. For example, organizations and people are separate subject matters that are relevant to a project's database. If one of the primary subject matters that the database tracks is organizations (or companies), then people who work for an organization are one of organization's characteristics (in addition to organization's name, address, etc). Because there may be more than one person per organization, people table should be linked to organization's table as lookup table (in one-to-many relationship). Similarly, people's table may contain both Yes or No type of fields and linked tables that characterize people.

Subject matters may function in different capacity. For example, people may be employees and have supervisors (both part of the people table - lets name it tblPeople). In such case Supervisors field (which is part of tblPeople) draws information as lookup field from the same table that it is located in (which is tblPeople). Similarly, employees may have different roles - for example, employees may both perform tasks in some instances and assign tasks to others in other instances. Here, too, people-related lookup fields, which in this case are part of the task-related subject matter's table, draw information from the same table (tblPeople). Further, to enforce referential integrity (if that is desired) the relationships should be created using duplicate issues of the same table in the Relationships window (in our example tblPeople). In case of queries (that are used as data sources for forms or reports) the same principle applies, but specific details of relationship building depend on whether self joins or multiple joins from one table to another are used.

There are exceptions to the "one subject matter in one table only" rule. Additional table is needed in the following cases: when the amount of fields is so large that it does not fit in only one table, or if certain entries have to be isolated for security reasons, or if the table contains entities with large number of different characteristics. For example, is supplier and customer organizations have both large number of very different characteristics, then it may make sense to have them in different tables. However, in most instances that is not the case.

When data storage structure (tables, table fields and relationships) is developed by following workflow, problems can result.

For example, if a database tracks different organizations (suppliers and customers) and data storage structure (tables, table fields and relationships) is developed by following workflow it may seem that different tables are needed for suppliers and customers. It seems only logical to think this way because the database does track movement of goods and services from suppliers to producers (who use the database) and then to the customers who use the final products. So, it seems like information regarding the suppliers and customers should be contained in different tables. Similarly, if within an organization the database deals with, let say, employees and supervisors, then it may seem like a good idea to have different tables for employees and supervisors. Or, if the database tracks how people from different organizations interact (lets say, as buyers and sellers, or as nominators and nominees) then it may seem like a good idea to have different tables for such entities.

However, closer examination usually shows that most fields match for the same subject matter, whether the subject matter is organizations or people or something else.

Separating a single subject matter (for example, organizations) into different tables increases complexity and decreases flexibility and number of options available for different operations. What may happen as a result is that the resulting database becomes difficult to develop so that information can be entered, queried, modified and displayed as needed. Of course, consequences are even more troublesome if normalization rules are violated so that any table contains fields that characterize more than one subject matter (for example, both people and organizations). That can also happen when data storage structure is developed by following workflow.

Thus, building data storage structure so that there is one and only one main table per subject matter helps to make application development a more efficient and productive process. In addition, in most cases there is no real need to set up more than one main table per individual subject matter. After all, whether an organization is a supplier or a customer, or whether a person works for a supplier or for a customer is simply a characteristic. Main subject matter tables can have literally tens of Yes or No type fields that characterize the subject matter included in the table. In addition, main subject matter tables can have literally tens of lookup tables attached to them for handling characteristics with more than one alternative. These lookup tables should contain applicable main subject matter characteristics' alternatives (for example, red, blue, yellow, etc). Each such lookup table should have its own automatically incremented primary key field in addition to the rest of the fields. Further, lookup table subject matter can also be characterized by using Yes or No type fields and lookup tables. Such structure can easily be worked with so that all the application development options are readily available while the necessary time and effort input is minimized.

Top

Summary

Workflow and subject matters are not the same. Many problems that are related to either data storage structure or user interface development can be avoided by applying the right principles to the right parts of application development.

These application elements that the users directly interact with should be developed based on workflow. For example, user interface and application's navigation structure should be developed so that interacting with the application produces coherent experiences for the users - that is, so that interacting with the application produces a "flow of information" rather than discontinuity and discontent. Accordingly, the latter should dictate which features and tools are made available to the users, just as it should dictate the development of content and application and page level navigation features and other user interface aspects.

Most relational database application's data storage structure should be built by connecting the subject matters that the application deals with and so that first the subjects become tables and their characteristics become table fields.