The po Data Model Frank Bergmann, 20060522

1
The po Data Model Frank Bergmann, 2006-05-22
This guide contains overview information that is
useful if you want to write reports or if you
need to extract information from the API. It is
not ment as a developer guide. Please check the
OpenACS manual for more detailed information.
2
Contents

• Other Documentation
• General Conventions
• "Categories"
• po Objects and Types
• Main Classes
• Financial Classes
• Auxilary Tables

3
Other Documentation

• This is a limited overview document.
• For more detailed information please see
• po Overview Diagram http//www.project-open.org
  /
• OpenACS Developer Guide http//www.openacs.org/do
  c/
• po Whitepapers http//www.project-open.com/whit
  epapers/
• po List of modules http//www.project-open.org/
  product/modules/
• po Developer Documentation http//www.project-o
  pen.org/doc/

4
General System Architecture

• po consists of three main parts
• A powerful object-relational data model
• TCL pages that render the contents of the
  database and
• TCL libraries that provide auxilary functions to
  the TCL pages
• The Data Model is particularly important in po
  because
• It is object-oriented
• The TCL layer does not contain objects. It merely
  renders the content of the Data Model
• The Data Model is the location where you need to
  go if you want to extract information from the
  system

TCL Pages
TCL Library
Data Model
5
General Conventions

• Tables names are always in plural form and
  written in lower case
• Tables are held in general in 2nd normal form
• Denormalized table columns are named with a
  "_cache" postfix.
• All main tables have an integer primary key
  lttable_namegt_id. This primary key references the
  table acs_objects which contains the type of the
  object.
• Most object-gtobject mappings are handleled by the
  "acs_rels" table and its subclasses (see below).
  Other mapping tables are called
  something_something_map
• Boolean fields have a "_p" postfix and contain
  't' or 'f' char values
• All main objects include "object_type_id" and
  "object_status_id" fields. "Type" is used to
  create minor subclasses of the objects, such as
  the distinction of im_companies such as
  "customer" and "provider". "Status" is used to
  deal with the object's lifecycle, such as
  "potential", "active", "closed" and "deleted".

Normal arrow means referential integrity
relation-ship between tables.
Big arrow tip means inheritance relation-ship. An
inheriting "object" references its parent class
with its object_id.
6
"Categories"
object
object_type

• Conventional/ traditional database design
• normally includes a lot of foreign key
• tables defining the type and status of an object.
• po takes a different approach in order to
• minimize the number of database tables and
• maintenance screens. We use a single
• "im_categories" table for all types of status and
• type information
• The total number of DB tables is reduced to a
  third or fourth
• A single maintenance screen is responsible for
  managing categories.
• Built-in features
• Localization
• Hierarchical categories
• Common GUI widgets
• Referential Integrity is enforced
• It is possible to assign the wrong Category to a
  field
• This has never happened in practice yet.

object_type_id
object_type_id
object_status_id
name
description
name
description
...
The "Classical" DB-Design Every table has it's
own tables for type, status and similar
information.
object
im_categories
object_type_id
category_id
object_status_id
category_type
name
name
description
description
...
po DB-Design All type and status information
is stored in a single "im_categories" table.
7
po Objects and Types

• All major tables in po and OpenACS are held
• as Objects.
• Objects are identified by an object_id.
• Information about object type is stored in the
  acs_objects table, together with metadata and
  creation information.
• "Being" an object has many advantages
• Permission system Associates objects with
  "privileges", including all necessary maintenance
  screens
• SQL metadata system Allows the administrators
  to dynamically extend object types with new
  attributes
• Full-Text searchIndexes all objects and
  produces generic URL for the search results
• Configurable workflowAllows to manage the
  status of any object. The WF includes a graphical
  WF editor.
• Generic Object Relationsacs_rels allows you to
  define generic relationships between objects.

8
po Objects and Types

• acs_attributes
• Each row in acs_attributes table defines an
  attribute of the specified object type.
• The contents of the table is managed by the
  "intranet-dynfield" package that contains a SQL
  metadata editor.
• acs_privileges
• Contains permissions "tokens" (gt privileges).
  These privileges allow a user to perform a
  certain operation in the system.
• acs_permissions
• Defines a mapping between acs_objects and
  acs_privileges.
• The mapping is per user group ("grantee_id").
  Groups can be hierarchical.

9
Main Classes
10
Main Classes

• im_biz_objects
• This abstract class defines a number of functions
  to manage "horizonal permissions" (gt Please see
  other po documentation) for project, companies
  and offices
• "Horizonal" permissions currently include roles
  such as "Project Manager" of "Full Member".
• parties, persons, users
• These three tables form an inheritance hierarchy.
  There is a view "cc_users" that unifies the
  information from these tables.
• im_freelancers, im_employees, users_contact
• These are "extension tables" for "persons" and
  define additional information for users with
  particular profiles.
• im_profiles
• This table defines the groups that are relevant
  for po, as opposed to potentially hundereds of
  groups of the underlying OpenACS system.

11
Financial Classes
Inheritance!
12
Financial Classes

• im_costs
• This is the main table for all cost items.
• All financial elements in the system are stored
  in this table. The table also receives updates
  from timesheet (cost related to users working on
  a project) etc.
• im_cost_centers
• Is currently not used. The table has been
  included in the current datamodel for smooth
  upgrade in future versions.
• im_invoices
• This table contains financial documents such as
  "Invoices", "Quotes", "Bills" and "Purchase
  Orders" (the name "im_invoices" is a bit
  misleading).
• im_invoice_items
• These are the individual lines of an invoice.

13
Relationship Classes
Defines the relationships between an
im_biz_object and some users. The membership type
corresponds to "horizontal permissions" and
usually includes "full member", "project
manager", "key account" and user-defined roles.
14
Relationship Classes

• acs-rels
• im_biz_object_members
• Defines the member of a im_biz_object and their
  roles (project manager of full member).
• Such a membership has an impact on the access
  rights of members to the business object.
• membership_rels Defines group-gtsupergroup
  relations

The acs_rels table is essentially a generic
mapping table for acs_objects. Once we come up
with a way to associate attributes with
relationship types, we could replace many of the
ACS 3.x mapping tables like user_content_map,
user_group_map, and user_group_type_modules_map
with this one table. Much application logic
consists of asking questions like "Does object X
have a relationship of type Y to object Z?" where
all that differs is X, Y, and Z. Thus, the value
of consolidating many mapping tables into one is
that we can provide a generic API for defining
and querying relationships. In addition, we may
need to design a way to enable "type_specific"
storage for relationships (i.e., foreign key
columns for one-to-many relationships and custom
mapping tables for many-to-many relationships),
instead of only supporting "generic" storage in
the acs_rels table. This would parallel what we
do with acs_attributes.
15
Translation Classes
im_trans_tasks
task_id
task_name
task_filename
task_status_id
project_id
task_type_id
source_language_id
target_language_id
subject_area_id
task_uom_id
invoice_id
quote_id
end_date
task_units
billable_units
trans_id
edit_id
proof_id
other_id
match_x
match_rep
match_100
match_95
match_85
match_75
match_50
match_0
note
16
Translation Classes

• im_trans_tasks
• This tables contains the description of the main
  activity related to translation projects.
• Includes information about Translation Memory
  repetitions
• Inludes information about the translator, editor,
  proof-reader and "other" assigned to perform this
  task (static workflow)
• im_trans_invoices
• This is currently just a flag (binary
  information) to identify invoices that have been
  created based on im_trans_tasks. Translation
  invoices are referenced by im_trans_tasks.invoice_
  id in order to make sure that every trans_task is
  included in exactly one invoice. These references
  need to be removed if a translation invoice is
  deleted.
• im_trados_matrix
• Contains the discounts per customer and
  translation task type for different Translation
  Memory matches.
• The trados_matrix associated with the "internal"
  company contains the site-wide defaults for
  customer translation prices.

17
Consulting Classes
im_projects
im_timesheet_prices
project_id
project_name
price_id
customer_id
company_id
task_type_id
project_type_id
material_id
start_date
uom_id
end_date
currency
price
...
note
18
Consulting Classes

• im_timesheet_tasks
• This tables contains the description of the main
  activity related to projects.
• im_timesheet_invoices
• This is currently just a flag (binary
  information) to identify invoices that have been
  created based on im_timesheet_tasks. Timesheet
  invoices are referenced by im_timesheet_tasks.invo
  ice_id in order to make sure that every task is
  included in exactly one invoice. These references
  need to be removed if a timesheet invoice is
  deleted.
• im_timesheet_prices
• Contains a price per material and customer.
• The timesheet_prices associated with the
  "internal" company contains the site-wide default
  price list.

19
Auxilary Tables

• GUI
• Define what "plugins" (these grey boxes) should
  appear on what page
• Defines the hierarchical menu structure of the
  system
• Define the columns for ListPages in the "core".
  The ListPages need to be extensible at runtime
  because they might have to accomodate new columns
  from add-on modules
• The list of all countries in the world
• The list of all currencies

20
po Overview
Trans-lation
HR
ApplicationModules
ProjectMgmt.
Other
Payroll
Project Subprojects
TranslationWorkflow
RoomReservation
Skill Database
ProjectControlling
TMIntegration
E-Commerce
TimesheetMgmt.
AutomaticInvoicing
Surveys
RecruitingWorkflow
Web-Mail
AvailableDocumentation
Glossary
CRM
Finance
Collaboration,Content KM
Calendar
FinanceBase
Filestorage
ContactMgmt.
FinanceGuide
Controlling
Chat
Wiki
Quotes Invoice
CustomerWeb Reg.
OnlineDiscussions
FreelanceInvoicing
TranslationWorkflowGuide
Mail ServerIntegration
WebDAV
Payments
MarketingCampaigns
IncidentWorkflow
TimesheetInvoicing
ContentManagement
FinancialReporting
CRMTracking
CMS
ForumGuide
Blog
FilestorageGuide
WorkflowEngine
Localization Framework
ReportingEngine
Portal Components
ApplicationServices
Mail ServerIntegration
ISDN TelIntegration
Full-TextSearch
SoftwareDevelopment
OOFrame
Security
OpenACSPermission
PackageManager
OO Model
PageContracts
PlatformServices
ConfigurationGuide
Templates
BasicAuthentication
ObjectMetadata
Profiling Performance
SQLTemplates
LDAPAuthentication
DynField Object Extensions
DebuggingSystem
AutomaticTesting
OpenACSDeveloperGuide
AutomaticSoftwareUpdates
AutomaticAudits
FormBuilder
SOAP XML-RPC
Operations Maint.Guide
System
Web Server
AOLServer
PoundRevers Proxy
CVS
DB-API
TCL
MondrianData-Warehouse
Oracle Intermedia/Text
TSearch2
SearchEngine
BigBrotherSys Mgmt.
OpenACSInst. Guide
Database
PostgreSQL
Oracle 8i, 9i, 10g
Database Replication
Postfix/ Sendmail
Unix InstallGuide
Linux
Solaris
BSD
Windows CygWin
Mac OS
OperatingSystem
21
Frank Bergmann frank.bergmann_at_project-open.com www
.project-open.com