Data Warehouse Design

The data warehouse design is coupled tightly with the structure of the ST36-based XML document. The relational database is the open source PostgreSQL system and makes use of schemas to organize content and organizational structure inside the database. See http://www.postgresql.org for further information on PostgreSQL-specific functionality. The following table lists the active schemas and their roles in the data warehouse.

Schema

Description

work

Tables used for loading and merging raw data – these will not be described below

reporting

Statistics pertaining to loading, updating, and indexing

xml

The complete XML broken into tables based on the ST36 XML structure

cdwsTables used for on-site citation and family functionality. See CDWS Schema and User-Defined Functions.
reportsAllows extraction of XML content from the tables in the xml schema. See Reports Schema Functions.
litigation (optional)Tables used for global litigation data provided by MaxVal. See Litigation Schema and User-Defined Functions.

Current Demos may not contain all of the schemas listed below. In addition, tables and schemas present in the current demo with the exception of reporting, work, and xml (described below), may be removed from the database without notice.


The following graphic is a tree view of the structure of a complete XML document. Each node represents a container whereby each leaf of the tree represents a table in the data warehouse (xml and work schemas). The naming pattern of tables follows the following rules:

  • dashes are replaced with underlines and
  • each leaf-node name is prefixed with t_, i.e., publication-reference =t_publication_reference.

The two exceptions to this are the non-XML table t_patent_document_values and the copyright node. The copyright container is stored separately in t_sys_data.

Note: The xml.t_designated-states table is deprecated and will be removed in a future schema release. designated-states data is found within the xml.t_international-convention-data table.

Schemas

Reporting

The reporting schema houses tables critical to data loading, update processes, and queues for optional indexing. Although most tables are for internal use, the table reporting.t_client_load_process contains interesting information for clients around which web status interfaces or triggers can easily be built.

Column

Type

Modifiers

Comment

client_load_process_id

serial

primary key

Table primary key

load_id

integer


The load identifer

load_source

text


Source data for load

url

text


URL of package to load

ndocs

integer


Number of documents contained in the load

entered_stamp

timestamp


Time entered for processing

completed_stamp

timestamp


Time loading completed

running_status

text


One of: downloading, unpacking, loading, merging, citations, priorities, complete

completed_statustext
One of: success, failure

If you have installed the optional Solr index, there is a companion table reporting.t_client_index_process that manages the indexing queue and has the exact structure of the reporting.t_client_load_process.

XML

The XML schema is the heart of the data warehouse and contains tables representing individual containers (sections) of the complete document stored in pure XML.

Table Structure–General

All tables, with the exception of xml.t_patent_document_values, follow the same structure.

Column

Type

Modifiers

Comment

<tbl>_id

serial

primary key

Table primary key

publication_id

integer

not null

Integer representation of the publication

modified_load_id

integer

not null

Internal load id used to manage data updates

status

char(1)


Validity of the XML fragment (v=valid, i=invalid)*

content

XML


The XML fragment

All XML stored in content columns is without exception well-formed but occasionally during processing, invalid XML is created. This is due to a variety of reasons, most predominantly, corrupt or invalid patent office data. In an effort to maintain the completeness of the publication, even invalid fragments are stored. If you wish to validate a complete document, simply filter out invalid fragments. 


The table xml.t_patent_document_values is the master content table containing meta information of the document in standard relational database columns. This table is the basis for the overall container element patent-document.

Column

Type

Modifiers

Comment

patent_document_value_id

serial

primary key

Table primary key

publication_id

integer

not null

Integer representation of the publication

created_load_idinteger
Load id of document creation

modified_load_id

integer


Load id of last modification

deleted_load_idinteger
Load id when document was deleted

status

varchar(16)


Status of complete document (new|update)

country

char(2)

not null

Country of publication

doc_number

varchar(32)

not null

Publication document number

kind

varchar(4)


Document kind code

lang

char(2)


Document publication language

ucid

varchar(32)


Concatenation of country-doc_number-kind

published

date


Date of publication

produced

date


Date of most recent Alexandria XML production, which may indicate original production or updates

withdraw

boolean


Withdrawn status

family_id

integer


DOCDB-provided simple family identifier

XML User-Defined Functions

In addition to content columns from which XML content can easily be selected, there are also the following supplemental functions available for common data extraction tasks:

xml.f_patent_document(integer, text)

This function returns an entire XML document given a publication_id as first argument. The second argument is currently unused.

SELECT xml.f_patent_document(1, null);


xml.f_patent_document_s(integer, text)

Similar to the function above, this function returns a SET OF XML documents, one document per row. Arguments are identical to function above.

SELECT xml.f_patent_document_s(t.publication_id, null)
FROM xml.t_patent_document_values AS t
WHERE t.published='20100407';


xml.f_ucid2id(text)

This function takes a ucid (unique character ID) and converts it to an integer publication_id.

SELECT xml.f_patent_document(xml.f_ucid2id('US-5551212-A'), null);