Tables

Table Structure

Each table has three audit fields: creator containing the database user name of the user who created the record; updated containing the last update time-stamp of the record; and updater containing the user name of the user who updated the record. These are changed by triggers.

matter

The central table of the system. For the purposes of this description, in many places we will consider a matter as a patent (which is the most complex matter the system will track).

• category_code: identifies the category of the matter (Patent, Trademark...). Key in the matter_category table containing the full name and eventual other details.

• caseref: is the inhouse reference used for identifying the patent family (i.e. this same reference will be used for the multiple patents of a same family). Manually entered or assisted in the UI with an auto-complete function incrementing the largest caseref in the table.

• country: two-letter ISO code, key in the country table containing the full names (plus pseudo-countries used in the patent system, like EP and WO).

• origin: code of the country or regional system the patent originates from (mainly EP or WO).

• type_code: code identifying the type of patent (divisional, continuation, etc). Key in the matter_type table containing the full name.

• idx: a counter for differentiating multiple patents in a family having the same type and the same country. Seldom used. Null by default (i.e. we don't want it displayed if it is not needed). Makes the combination caseref+country+origin+type+idx a unique identifier, call it UID.

• parent_id: key to a parent patent (mainly for divisionals, continuations, and EP or WO national phases). A priority patent shall not be set as a parent – it is instead identified by the “Priority Claim” event in the later filed patent.

• container_id: identifies a “container”, an earlier filed patent that contains common information that will be shared with the current patent. The choice will be given to the user, upon creation of a new patent in the UI, if it should duplicate all information from the parent or priority patent (for later modification, such as changing the inventor list), or if the parent or priority patent should be used as a container for the common information. If the parent already has this field set, the child simply copies the container_id value over. The priority patent normally doesn't have this field set. All classifiers are shared with the container. No events or tasks are shared with the container. Identified actor roles will be shared. The shared actor/role information is flagged by matter_actor_lnk.shared, this flag being set if the actor_role.shareable flag is set. So, when the container_id field is not null, it is used instead of the ID field for linking to the flagged actors in matter_actor_lnk. The unflagged actors are still linked to by the ID field.

• responsible: database user name of the person responsible for the matter, who is also listed in the actor table (actor.login) and the User table.

• dead: flag indicating that the patent is no longer watched. When set, no further tasks are created and the pending tasks are not tracked in the home page.

• notes: ditto.

• expire_date: date at which the matter expires. Calculated from a pseudo-task “EXP” in the task_rules table.

• term_adjust: mainly for US patents. Indicates the number of days by which the expiry date is extended.

matter_category

Contains the full names of categories.

• code: key.

• ref_prefix: prefix for automatically generated caseref values. Upon creating a new matter in this category, the new caseref is calculated as the largest existing caseref starting with this prefix, plus 1.

• category: full name.

• display_with: display in the same tab as the category identified here in the user interface.

country

Contains the two and three character ISO codes of the countries, the full names in German, English and French, and the number codes. There are two additional fields, ep and wo, which are flags indicating that the country is a default pick-list element in nationalizing an EP application and a WO application, respectively.

actor

The field names are self-explanatory, except maybe:

• display_name: when not null, this field is displayed in the UI instead of first_name and name.

• login: the database user name for this actor.

• parent_id: identifies a “parent” company, to help track related companies.

• company_id: for persons, identifies the company the person works in. This field is copied into matter_actor_lnk.company when the person is linked to a matter.

• site_id: identifies the site (in fact a company) the actor works in. For large companies having several places of business.

• warn: a flag that will cause the actor to be highlighted in the UI.

matter_actor_lnk

Links actors to matters and provides additional information specific to each link.

• matter_id: identifies matter.

• actor_id: identifies actor.

• display_order: indicates in what order the actor should be displayed in a list of actors having the same role.

• role: code identifying the actor's role. Links to actor_role.code, table containing the full role name and other information for the role.

• shared: flag indicating whether the actor is to be inherited by later created matters in the same family.

• actor_ref: the file reference of the actor (when the actor is a client or an agent).

• company_id: a copy of actor.company_id. Kept for tracking the company at the time the link was created. Useful if the actor changes companies.

• rate: the participation rate of the actor (for inventors and applicants/owners), when several actors are listed with the same role.

• date: a date for tracking change of ownership, for instance (used when the role is “Ex-Owner”).

actor_role

Contains the full names and other information on available actor roles for the matter_actor_lnk table.

• name: full name of the role.

• display_order: order in which the role names are displayed in the UI.

• shareable: flag copied into matter_actor_lnk.shared when the actor is assigned to a matter with this role.

• show_xx: flags indicating whether the "xx" information from matter_actor_lnk should be displayed or editable in the UI.

• box: (not used) box number in which a set of consecutive roles is displayed in the UI.

• box_color: (not used) color of the box. If different colors are set for roles displayed in a same box, the first color is used.

• notes: ditto.

event

This table lists all the events and their details for each matter.

• code: code identifying the event name. Links to table event_name containing the full name of the event and other details.

• matter_id: identifies the matter to which the event is assigned.

• event_date: ditto.

• alt_matter_id: links to matter.id to identify another patent to which the event is related, especially a “priority” patent.

• detail: contains additional information specifying the event. For instance the numbers assigned upon filing, publication and grant.

• notes: ditto. Displayed in a bubble when hovering the mouse cursor over it in the UI.

The “Priority Claim” event (code PRI) requires special treatment. The alt_matter_id will identify the patent from which priority is claimed, if that patent is also present in the matter table (hopefully). In that case the event_date is copied from the “Filed” event of the priority patent. If that “Filed” event is absent, thus a null date returned for the event_date, today's date is used instead - a trigger will update it automatically when the "Filed" event is created in the priority patent. Note that this date inserted in the "Priority Claim" event is just a place holder. The UI will display the actual date of the "Filed" event of the priority patent, and base all calculations on that date.

If the priority patent is not available, the alt_matter_id field will be null and the “Priority Claim” event treated like any other event. In this case you will enter an event date and detail (patent application number) as for any other event.

Same thing for the "Parent Filed" event (code PFIL), which refers to the filing date of a parent patent (for divisionals and continuations).

task

A task is always linked to its trigger event. A manually created task will thus be created from an existing event in a matter. The related matter is found through the event the task is linked to (event.matter_id).

• trigger_id: identifies the event that triggered the task.

• code: code identifying the task name. Like for events, it links to table event_name containing the full name of the events or tasks and other details.

• due_date: ditto.

• assigned_to: database user name of the person responsible for the task. If null, the person responsible is the one identified in the matter (matter.responsible). When this field is set to a user, the task is visible in the "Mine" list of the user's home page.

• detail: an additional qualifier for the task.

• done: flag indicating whether the task is done. If set, the task is no longer displayed in "to do" lists of the UI.

• done_date: date of completion of the task.

• rule_used: for an automatically calculated task, identifies the rule ID in task_rules that was used to calculate this task.

• time_spent: the time spent on the task can be inserted here.

• notes: ditto. Displayed in a bubble when hovering the mouse cursor over it in the UI.

• cost, fee and currency: used to track invoicing information for the task

event_name

Contains the full names of events and tasks, and other information.

• code: key.

• name: ditto.

• category: (not used) category to which the event or task is specific. Initially intended for targeting pick-lists in the UI.

• country: (not used) country to which the event or task is specific. Initially intended for targeting pick-lists in the UI.

• is_task: flag identifying whether this is a task. Used for targeting pick-lists in the UI to events or tasks, whichever applies.

• status_event: flag indicating whether the event should be tracked as a status of the matter and displayed in the status box of the UI.

• default_responsible: used for tasks. Identifies the user to whom the task is assigned to by default, i.e. the database user name contained in this field is inserted by default in the assigned_to field of the task.

• use_matter_resp: used for tasks. Flag indicating that the matter responsible user is by default inserted in the assigned_to field of the task. Useful for identifying tasks that the matter responsible wishes to track specifically under the "Mine" list of his home page.

• unique: flag indicating that the event can only appear once in a matter. For tasks, means that only one task calculated with the same rule (same rule_used) can exist in the matter.

• uqtrigger: flag for tasks, indicating that multiple tasks of this type in a same matter must share the same trigger event. This flag was added for situations where there is a task set by default for all countries (for_country=null), and the same type of task set for specific countries, with a different trigger event. Then, if the countries of the matter and the task match, the country specific task is taken instead of the default task.

• killer: flag indicating that the event terminates the life cycle of the matter. Sets the matter.dead flag, whereby all pending tasks are no longer tracked and no further tasks are triggered in the matter.

• notes: ditto.

classifier

Contains all other information that one would like to see in a matter, like titles, descriptions, abstracts, links, etc.

• matter_id: identifies the matter.

• type_code: identifies the classifier type name. Links to table classifier_type containing the full name of the classifier type and other details.

• value: free-text value for the classifier.

• url: contains a URL. If not null, the UI displays value as a link to this URL.

• value_id: link to the classifier_value table. If null, the above value field is displayed in the UI. If not null, the identified value taken from the classifier_value table is displayed.

• display_order: order of display for several classifiers of same type.

• lnk_matter_id: links to a related matter. The value or value_id may be used to specify how the matter is related. The UI displays the linked matter's UID.

classifier_type

Contains the classifier types usable in the classifier table.

• code: key.

• type: full type name.

• main_display: flag. When set, the classifier should be displayed in the main area of the matter detail display (like titles, trademark names, etc).

• for_category: used for classifiers of the main display area in order to limit the possibilities of what the user can add to that area, based on the matter's category.

• display_order: order of display of the type when several types are displayed in the UI.

• notes: ditto.

classifier_value

Contains a list of possible values among which the classifier can be chosen. Used to restrict the values available to given classifier types.

• value: ditto.

• type_code: identifies the type (in classifier_type) for which the value can be used.

• notes: ditto.

task_rules

Each record in this table is a rule for automatically creating tasks from trigger events. The table is crafted manually. It is checked every time an event is created, updated, or deleted for a matter that is not dead and has no killer event.

Tasks created for new events through these rules have their task.rule_used field set to the ID of the rule that was used.

If a trigger event (identified by task.trigger_id) is updated in the event table, the related open tasks (i.e. having a non null rule_used field and their done flag unset) have their due_date recalculated using that same rule.

If a trigger event is deleted, the related tasks are deleted too.

The due_date of the task is calculated with the days, months, and years fields from the trigger event's event_date. NOTE that the task is created only if the calculated due_date is in the future.

If multiple events corresponding to trigger_event exist in a patent (which is possible for the “Priority Claim” event (PRI)), the earliest date is used for calculation.

• active: flag, set by default, indicating that the rule should be used. When unset, the rule is skipped. Useful for debugging and maintenance purposes.

• task: code of the task to create in the matter (links to the event_name table).

• trigger_event: code of the event that triggers the task (links to the event_name table).

• clear_task: flag. When set, any open task identified by the task field is cleared in the matter (i.e. its done flag is set and its done_date is set to the event_date of the trigger event).

• delete_task: flag. When set, any task identified by the the task field is deleted in the matter, but only if the matter depends on a container.

• for_category: code of the matter category to which the task is specific. If empty, the task applies to all categories.

• for_country: code of the country to which the task is specific. If empty, the task applies to all countries.

• for_origin: code of the origin to which the task is specific. If empty, the task applies to all origins.

• for_type: code of the matter type to which the task is specific. If empty, applies to all types.

• detail: additional information to specify the task.

• days, months, years: specifies the time interval added to the trigger event date to calculate the task due_date.

• recurring: (not implemented) flag. When set, indicates that the task repeats periodically. The recurrence period is indicated in the days/months/years columns. Only one task is calculated at a time: when the current task is closed, the next one is calculated.

• end_of_month: flag indicating that the due_date is set at the end of the month in which the calculation falls.

• abort_on: event code. If an event identified by this field is present in the matter, the task is not created.

• condition_event: event code. The task is created only if an event identified by this field is present in the matter.

• use_parent: flag. If set, use the “Parent Filed” event_date, if it exists, for calculation.

• use_priority: flag. If set, use the earliest “Priority Claim” event_date, if it exists, for calculation.

• use_after: the rule is used only if the trigger event date is after the date indicated in this field. If null, the rule is always used.

• cost, fee and currency: used to set default invoicing information – copied into the created task.

• warn_text: (not used) contains a message to display in a message box of the UI when the task is created. If empty, no message box is displayed.

• notes: explanations on the rule.

default_actor

Defines actors that are automatically inserted in newly created matters.

• actor_id: ID of the actor to insert in the matter.

• role: role of the actor to insert.

• for_country: code of the country where the actor should be inserted. If NULL, the actor is inserted in all countries.

• for_client: not implemented.

• shared: flag indicating whether the actor should be shared when the new matter is a container.

Views

There are a few views provided with the phpip schema. Some were created for our own needs, but some are "system" views that are used by triggers and the PHP code, so you should not delete them or change them without knowing what you're doing. These system views are:

task_list

event_lnk_list