@Data-Model Overview

The ]project-open[ data-model consists of about:

Due to ]po['s modular architecture, you only need to understand a small part of this data-model in order to access data or to produce custom reports. In addition, we have put great effort in providing you with tools to access the data-model in an easy way:


Data-Model Tutorial


Data-Model Clickable Map

 

The clickable map below provides a schematic overview over some core parts of the ]po[ data-model.

 

 

  

General Conventions

  • Table names are always in plural form, written in lower case, and delimited by the underscore character "_"
Company -> im_companies
Office -> im_offices
Project -> im_projects
  • Table names pre-fixed with "im" are ]project-open[ specific, non-prefixed/"acs" pre-fixed table names are inherited from OpenACS 
Timesheet Tasks (]project-open[) -> im_timesheet_tasks
Party (OpenACS) -> parties
Object (OpenACS) -> acs_objects
  • Tables (generally) conform to the 2nd normal form
  • Denormalized table columns are named with a "_cache" postfix.
  • All main tables have an integer primary key <table_name>_id.  This primary key references the table "acs_objects" which contains the type of the object.  This id is not plural and need not be prefixed.

im_projects -> project_id
groups -> group_id
  • Most object-object mappings are handled by the "acs_rels" table and its subclasses.  Any additional auxiliary mapping tables are called "something_something_map".  
  • Boolean fields have a "_p" postfix and contain 't' or 'f' char values
Example Attribute from im_costs

variable_cost_p      bpchar   

  • All main objects include "object_type_id" and "object_status_id" fields. "Type" is used to create minor subclasses of the objects, for example the distinction between "customer" and "provider" for im_companies objects.  "Status" is used to deal with the object's lifecycle, such as "potential", "active", "closed" and "deleted". 
Example Attributes from im_tickets

ticket_status_id   int4
ticket_type_id     int4

Main Classes

 

 

Object - acs_objects

The root table for the acs object hierarchy.  It all starts here folks.

Business Object - im_biz_objects

Most data and the most important objects in the ]project-open[ data model are derived from and structured as "Business Objects".  A business object is just a database table which adheres to the following conventions.

  • A integer primary key called <object>_id representing a unique object ID
  • The primary key references the "acs_objects" table, where meta-data about object type, creation date, etc. is stored
  • Two integer keys <object>_type_id and <object>_status_id 
  • These two keys reference the "im_categories" whose function is explained in more detail below.

Business Object Sub Classes:

Office - im_offices

Represents physical office locations and their data attributes.

Company - im_companies

Represents commercial organizations, what their relation is to your company, contact info, their industry, etc.

Project - im_projects

The "project" concept and running a business using project-management principles is a fundamental to ]project-open[ and it's construction and operation.  The majority of actions and activities in ]project-open[ center around project objects, which store data about projects, like creation date, final customer destination, manager, etc.

Categories - im_categories

Category Listing - see all the available ]project-open[ category types and their descriptions

Conventional/traditional database design normally includes many foreign key tables defining the type and status of an object.  For example, to capture all the different stati and types of "im_projects" we should create two new tables "im_projects_status" and "im_projects_type".  As a result, for every new table x we would like to introduce there is at a minimum 2x more tables to create to track their type and status, possibly more for a complex object.  

]project-open[ has taken a different route in our database design model.  In order to save space and maintenance efforts, all object type and status id's are stored in one single table, "im_categories" the look-up table for all individual object's to consult.  We believe this design offers the following advantages over the classical model:

  • The total number of database tables is reduced by a ~third
  • A single maintenance screen is responsible for a managing categories
  • Built-in localization, hierarchical categories, and common GUI widgets
  • Referential Integrity is enforced

We do acknowledge that it is possible to assign the wrong category to a field, however this mistake has not happened before by any programmer and it is a relatively easy error to detect.

Within the context of ]project-open[ "Categories" are a general concept being used throughout the system for several different purposes. 

  • Sometimes categories are used to hold values which should be modified by a knowledgeable user (“user”).
  • Sometimes categories are supposed to be configurable by system administrators (“admin”).
  • Finally, categories are used as a kind of “internal system constant” that should never be touched, not even by a system administrator (“constant”).

Category Hierarchy
Categories can be ordered hierarchically using an "is-a" relationship. This lets you define category-subcategory relationships.
The "is-a" relationship represents a "transitive closure" of a "parent-child" relationship. "Transitive closure" means that all parents of a category need to be entered in an "is-a" field, not just as the "direct parent".  This way of defining the category hierarchy has several advantages such as:  

  • "Multiple inheritance" – A category can have more then one parent.
  • Fast "is subcategory" query – The SQL query to determine whether a category is a subcategory is very fast, because all cases are stored in the "is-a" relationship. In particular, this avoids any hierarchical/iterative sub queries.

However, there are also several disadvantages:  

  • Complex – This scheme is difficult to understand for non-administrators
  • Error prone – Nothing prevents you from defining a cyclic "is-a" relationship, possibly leading to infinite loops in the system

Please make sure you understand the concept of a "transitive closure" before modifying categories yourself. 

Category Example - Company Type - company_type_id

The table below shows an example of the “Company Type” category. This category is supposed to be editable by a sysadmin and shows the general structure and function a “category”.

 ID  Category  Is - A
Description
 51  Unknown    Use this if the type of the company is not yet clear (to be clarified later).
 52  Other    Use this for strange cases where you really don’t know.
 53  Internal  Customer  Use this type to denote your own company or companies belonging to your group or holding.
 54  MLV Translation Agency Company
 Customer  A “Multi-Language Vendor” translation agency.
 55  Software Company
 Customer  A software company as a customer.
 56  Provider    Super class of all providers.
 57  Customer    Super class of all customers.
 58  Freelance Provider
 Provider  A provider that consists of a single freelancer.
 59
Office Equipment Provider
 Provider
A provider for furniture and other office equipment.

 

Categories consist of the following fields:

  •  A human readable “Category” field
  • An ID that is sometimes used as a constant for internal system purposes
  • An “Is-A” field that defines a hierarchical tree (more precisely: directed acyclic graph) relationship
  • A “Description field” to define the precise semantics of a category.

The “Company Type” category is designed to be extended by “knowledgeable users”, so that new types of customers can be added during the lifetime of the system.  However, such a “knowledgeable user” would need to know that he or she couldn’t touch the “Internal”, “Provider” or “Customer” fields, because their IDs are referenced as constants internally in the system.

For instructions on how to access the administration menu to view/modify/add categories in ]project-open[ consult below.

Complete and Detailed Object-Type Map

OpenACS provides an administration tool that compiles an automatic map of all the object-types and their hierarchical relation.  However, it is not perfect and some database objects are hidden.  Most notably you will see that "im_categories" does not appear.  Unfortunately we cannot provide a link to see this in real time on the demo server, because you need "SysAdmin" priveleges to see this.  Here are the instructions on how to access the Object-Type Map from your own ]project-open[ installation.  You must have Admin privileges.

1.  The OpenACS Admin Menu is slightly hidden, there is no direct access from internal ]project-open[ screen, you must type the address "myserver/admin" to see the following screen.  

2.  Click on the "Object-Type" Link and you will be directed to listing of object-types.  Clicking on each individual object will display all of it's variable attributes and inheritance.

Category Listing and Administration Menu

From the ]project-open[ admin home page you will find a link to the category manager.  You must have admin privileges to view and manipulate the categories.  Please do not attempt to change/add categories unless you fully understand first and have read the PO-Configuration Guide, specifically the section on Categories, or have read the relevant wiki entries about categories on this page, or the Category page.  Some category types are mandatory and should not be edited.

1.  From the ]project-open[ Admin Menu click on "Categories" or proceed directly to the menu screen with the URL "myserver/intratnet/admin/categories"

 

2. Use the filters to search for category types and view their corresponding categories.  The "category" vs. "category type" distinction is: 

  • Add a new Category - within a pre-existing category type, add a new a category
  • Add a new Category for a new Category Type - create a new category type, and add a new category to the newly created category type.

 

3.  Please note:  Categories are frequently cached for performance reasons.  You may have to restart the server after adding or     modifying a category in order for the changes to take effect.  Or you could flush the memory cache.

 

  Contact Us
  Project Open Business Solutions S.L.

Calle Aprestadora 19, 12o-2a

08902 Hospitalet de Llobregat (Barcelona)

Spain

 Tel Europe: +34 609 953 751
 Tel US: +1 415 200 2465
 Mail: info@project-open.com