PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 2
PPDM Lite 1.0 - Architectural Principles
1.
Introduction 'he PPDM Lite Architect$ral Principles doc$ment contains the r$les and &$idelines that &overn the development of the PPDM Lite data model Architect$ral Principles establish proced$res for namin& tables# col$mns and constraints 'hey also provide &$idelines abo$t ho col$mns sho$ld be formatted and ho common s$bject areas# s$ch as *eodetics or +nits of Meas$re sho$ld be mana&ed
1.1
/enefits Architect$ral Principles are developed to ens$re that disparate s$bject areas ithin PPDM Lite have a consistent ,loo- and feel. 'hese benefits facilitate $pta-e and implementation of the model for $sers in reso$rce!based ind$stries# and enable individ$al $sers to $nderstand and $se the data in the model str$ct$re ith a hi&h de&ree of acc$racy
1.2
PPDM Architectural Principles and pen !tandards PPDM or- &ro$ps or the Modelin& /ommittee ma-e recommendations for additions or enhancement to the Architect$ral Principles 'hese are revieed by the Modelin& /ommittee in li&ht of the impact to the folloin& areas0 •
Adherence to open standards
•
1mpact on $sers of vario$s data base platforms (racle# Sybase# Access etc)
•
1nte&ration or modification re3$ired to e4istin& model str$ct$res
•
5ffort needed for development or conversion of softare by members
•
5ffort re3$ired for implementation or conversion of data models by members
•
5ffect on performance# $sability and $nderstand!ability of the model
•
/ost to implement recommendation in the model
'he PPDM Association is committed to the development of an open data model that is not dependent on any vendor or softare prod$ct for implementation S6L 72 A8S11 standards provide the c$rrent fo$ndation for the PPDM Lite version 9 Architect$ral Principles: this enables the Association to $tili;e open standards that are independent of any Data base vendor
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 3
PPDM Lite 1.0 - Architectural Principles =$t$re desi&n of the data model is li-ely to re3$ire additional data base f$nctionality /$rrent plans are to revie and adopt additional capability as defined in S6L 72 and S6L> standards as re3$ired 1ncorporation of si&nificantly chan&ed or enhanced Architect$ral Principles represents a bench!mar- for the PPDM Association in that they represent si&nificant chan&es that are e4pected to provide s$bstantial benefits for the membership Mi&ration to ne models# partic$larly hen ne methodolo&ies are employed# can be diffic$lt# time cons$min& and e4pensive for the membership 'he fre3$ency ith hich this is done ill be minimi;ed by the Association# ith caref$l consideration to the impact amon& the members /onse3$ently# si&nificantly chan&ed Architect$ral Principles ill be implemented as part of a ne Architect$ral %elease of the model (ie version 2?)
1.3
Interatin PPDM ull and PPDM Lite Since 97@7 PPDM Association has developed a rob$st# complete data model for the 5ner&y b$siness 'he most recent version of this f$ll data model# PPDM ># contains over 92 tables in " s$bject mod$les PPDM Lite is developed primarily to allo implementers to create a standard# li&ht ei&ht# rolled $p version of PPDM > (PPDM f$ll) PPDM lite contains s$mmary information from a broad spectr$m of s$bject areas# each of hich is relatively shallo in it.s covera&e Most tables in PPDM Lite contain heavily denormali;ed s$mmaries that are derived from PPDM > Mappin&s beteen PPDM > and PPDM Lite are available to members of the PPDM Association
1.(
!upport and /usiness Modules in PPDM PPDM consists of an inte&rated set of b$siness mod$les that are desi&ned based on the re3$irements of the or- &ro$ps and s$pport mod$les that are desi&ned to enhance and s$pport the information provided by the b$siness mod$les A PPDM or- &ro$p# thro$&h the PPDM modelin& process# $nderta-es B$siness Mod$le desi&n Major revisions $s$ally occ$r hen the or- &ro$p is reconvened to enhance the scope of the model 1n contrast# the desi&n of s$pport mod$les may develop over several modelin& cycles thro$&h re3$irements defined by many or- &ro$ps 1n some cases# a or- &ro$p may be convened that converts a mod$le from a simple s$pport mod$le into a f$ll!scale b$siness mod$le in its on ri&ht
1.)
PPDM DDL Co"ponents PPDM Data Definition Lan&$a&e (DDL) is provided to members in the folloin& formats0
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 4
PPDM Lite 1.0 - Architectural Principles
Post&reS6L
'he DDL is separated into a n$mber of files 5ach component is described belo
1.).1 Mandator# Co"ponents 'able and col$mn definitions C file e4tension is tab /onstraints (primary# forei&n# chec-) C file e4tension is con
1.).2 ptional Co"ponents 'able comments C file e4tension is tcm /ol$mn comments C file e4tension is ccm 'able synonyms C file e4tension is syn Spatial e4tensions C added $sin& proced$res provided by PPDM
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e
PPDM Lite 1.0 - Architectural Principles
2.
Chane !u""ar# 'he folloin& sections of this man$al have been added or revised for this version of the Architect$ral Principles associated ith PPDM Lite0
Title
Chane t#pe
16
Extensibility
Added in the section from 3.7
1.X
Changed 1.x to 1.0 and reviewed for !raft" comments
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e /
PPDM Lite 1.0 - Architectural Principles
3.
%a"in and Desin Con&entions
3.1
becti&es 5stablish namin& conventions that provide a consistent reference hen accessin& components of the model andor database 8ames $sed sho$ld0 •
Ma-e sense
•
Be consistent thro$&ho$t the model
•
+se accepted b$siness terminolo&y here this is very consistent and standard in ind$stry
•
Be named differently than the PPDM > data model so that PPDM Lite data model can co!e4ist in the same database schema# if necessary
3.2.1 !tructure 8amin& of tables sho$ld reflect the core PPDM > table name 1deally# the table name ill only be altered to incl$de a ,LE. at the be&innin& 'he str$ct$re of a table names is0
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e
PPDM Lite 1.0 - Architectural Principles 3.2.$ !#non#"s 5ach table is assi&ned a name and a synonym in the /AS5 tool Synonym names are released as an optional se&ment of the DDL in a file names GSH8 Synonyms are named so as to &ro$p tables into lo&ical components All synonyms ill be prefi4ed ith an ,L. Primary -ey and forei&n -ey names are based on the synonyms
3.2.' Table co""ents 9 5very table m$st have a complete description e4plainin& hat it is 1f it is a direct copy from PPDM ># then the description ill be the same 1f it has been altered# then the description ill also be altered 2 'able lon& names alays appear as the first part of the table comments (in $pper case)# folloed by a colon (0) 54ample0 # $E## %E&'(CE) *he # $ell %ervice table contains common attrib+tes fo+nd in all tables that are associated with ,$( or well data. *his commonality allows a single table to be created in this --! #ite data model beca+se the definition of #ite is to /rovide a +ic in2site or overview of data that is available with the +nderstanding that more in2 de/th data for analysis4 is available in the f+ll --! data model.
3.2.+ Table lenth Some relational databases impose a limit on the ma4im$m si;e of a table S6L Server 2 limits the total alloed len&th to @I bytes 5ven tho$&h yo$ can s$ccessf$lly create a table in S6L Server 2 that co$ld theoretically contain more than @I bytes# yo$ ill be $nable to act$ally add or $pdate a record ith more than @I bytes of data S6L Server 2" does not have this limitation 'able desi&n m$st allo entry level S6L72 compliant relational database implementations to achieve 9J *old level compliance: for this reason# the total len&th of a table may not e4ceed @I bytes
3.3
Colu"ns PPDM Lite 9 col$mn names are based entirely on the so$rce PPDM > col$mns that they are derived from 1n the case of a mod$le or section bein& derived from an e4ternal data model# best efforts ill be made to follo the PPDM Architect$ral Principles as laid o$t for PPDM > 'his also applies to col$mn comments 1n &eneral# the same namin& conventions apply for col$mn names as do for table names e4cept that PPDMLite col$mns are not prefi4ed by ,LE. and are limited to 2 characters in len&th
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e
PPDM Lite 1.0 - Architectural Principles 3.3.1 DATA !*8C as a colu"n All PPDM Lite tables contain a reference to the DA'AES+%/5 So$rce is ta-en to mean the direct system or application so$rce of the data that as loaded into this ro 1n the case here PPDM Lite is considered to be a master data store# DA'A S+%/5 may represent the ori&inal so$rce of the data
(.
Constraints in PPDM %eferential 1nte&rity refers to the development of enforced (mandatory or optional) relationships beteen parent and child tables 1n a relational database# the concept of %eferential 1nte&rity is critical in order to maintain the val$e of the information contained ithin the database 1t ens$res that database information is associated in a consistent and meanin&f$l fashion =or e4ample# %eferential 1nte&rity prevents a Well 'est from bein& loaded $nless the loader can determine hich ell the test belon&s to 1t also forces data loaders to validate entries here needed# so that variations or typo&raphic errors in names (54 Alberta# Ablerta# Alta# Ab) do not create data retrieval problems later on =or a detailed disc$ssion of constraints in PPDM# refer to the PPDM %eference *$ide on constraints# available on the PPDM eb site
(.1
Pri"ar#
(.1.1 becti&es 'he Primary -ey of a PPDM table consists of a col$mn or &ro$p of col$mns that $ni3$ely identify each ne occ$rrence or ro of data in the table /ascadin& Primary Keys 1n PPDM may also be $sed to carry -ey identification val$es that are commonly 3$eried don into s$bordinate tables Primary -eys in PPDM sho$ld be enforceable $sin& native S6L only# altho$&h tri&&ers or proced$res may be necessary to &enerate s$rro&ate components in some cases
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 1&
PPDM Lite 1.0 - Architectural Principles •
1f the n$mber of col$mns in the nat$ral identifier becomes very lar&e# it may be replaced (all or in part) by a s$rro&ate -ey ! this prevents the mi&ration of an $nieldy -ey 1n this case# the nat$ral identifier m$st be identified and a $ni3$e inde4 created for the nat$ral identifier
•
1f the val$e of the nat$ral identifier is not -non at the time the ro is created or if there is no nat$ral identifier# s$rro&ate -ey components s$ch as E1D# BSE8 or S56E8 may be added as a component of the Primary -ey
All components of the Primary -ey are mandatory " DATE fields sho$ld not be $sed as Primary Key components# since many sites may not -no the val$e of a date at load time I MEASURED VALUES, s$ch as D5P'
(.2
orein
(.2.1 becti&es =orei&n Keys are constr$cted to ens$re that forei&n -eys can be implemented and enforced $sin& only native S6L henever possible 'o s$pport &ood data mana&ement practices
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 11
PPDM Lite 1.0 - Architectural Principles 2 +nless the =orei&n -ey col$mns are part of the Primary -ey# they sho$ld alays be defined as 8+LLABL5 > 1t is acceptable for the PPDM Lite tables to have col$mns that are bo$nd by more than one constraint
(.3
Chec= constraints
(.3.1 becti&es /hec- constraints can be $sed to enforce the str$ct$ral inte&rity of the data model# or to provide validation for certain -inds of val$es
(.3.2 ;uidelines All chec- constraint val$es sho$ld be provided in +PP5% /AS5 only PPDM or- &ro$ps may# based on the folloin& principles# define constraints0 9 'he chec- constraint may be $sed to enforce a s$per!s$b type implementation as described in the section on arcs 2 al$es that the col$mn may contain are part of a small set# -non at desi&n time# and not s$bject to chan&e =or e4ample# col$mns named JE18D may only contain the val$es H or 8 (or 8+LL) > 'ables that are projected at the s$pertype and have a col$mn to specify the s$btype that is described =or e4ample# the B+S185SSEASS/1A'5 table $ses the col$mn BAE'HP5 to define if the ro refers to a P5%S8# %5*+LA'%H A*58/H# /MPA8H or /8S%'1+M
(.(
Indees
(.(.1 *ni>ue Indees Sin&leton col$mns ith s$pportin& $ni3$e inde4es are $sef$l in many circ$mstances =or e4ample# the spatially enablin& methodolo&y created in 22 C 2> re3$ires the $se of a $ni3$e identifier for $se in 5S%1.s *eodatabase
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 12
PPDM Lite 1.0 - Architectural Principles 'he PPDM Association has provided early s$pport for &lobally $ni3$e inde4es in PPDM version > thro$&h a col$mn called PPDME*+1D (varchar2(>@)) 'he defa$lt DDL scripts ill have this col$mn as a n$ll col$mn 'here ill be an additional script s$pplied that ill0 ! convert all PPDME*+1D.s to 8ot 8$ll# ! chan&e the defa$lt to an racle call that ill pop$late the ro $pon insertion# ! add a $ni3$e inde4 on every col$mn DDL commands to do this are incl$ded in the PPDM > data model deliverables 'he PPDM compliance meas$rement process ill be modified to ens$re that implementations that $se this are not penali;ed for modifyin& the DDL
(.(.2 Inde n orein
(.(.3 ;uidelines 9 PPDM ill not attempt to provide a set of performance inde4es 2 PPDM ill provide inde4 DDL for forei&n -ey col$mns 1f necessary# PPDM has a script that can be $sed to &enerate appropriate inde4es and then reverse en&ineer inde4 DDL from an installed database PPDM ill not provide inde4 DDL for non!forei&n -ey col$mns
(.)
8ecursi&e relationships %ec$rsive relationships# hich allo entries in a PPDM tables to refer to other entries in the same table# occ$r in to forms0 9 Recursi#e relations$i!s %Pig&s ears' ! these relationships are stored in the parent table =or e4ample# the W5LL table contains a rec$rsive reference to PA%58'EW5LL 2 (rea)out ta*les ! these relationships are stored in a separate table# ith to relationships constraints to the same parent table 'hese relationships may be either 909 or 90m ! the implementation is the same
(.).1 ;uidelines Methods for 3$eryin& these str$ct$res cannot be developed reliably $sin& S6L 72 standards (see the PPDM +ser *$ide on /onstraints for details) 1n this case#
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 13
PPDM Lite 1.0 - Architectural Principles PPDM allos $se of proprietary 3$ery mechanisms s$ch as racle.s /885/' BH synta4 or Sybase.s proced$ral method
(.$
%a"in Con&entions 'he Association has developed proced$res that ill modify the names assi&ned by the /AS5 tool to conform to the namin& conventions in this doc$ment 9 /onstraint name may not be the same as a table name 2 All constraint names m$st be $ni3$e > /onstraint name e3$als the short name (Synonym) of the host table and the short name of the so$rce table concatenated ith a -ey type desi&nation (ie L(A+LELL+K for a forei&n -ey) =orei&n -ey constraint names sho$ld indicate the relationship and end in E=K# here n is a se3$ence n$mber assi&ned to the forei&n constraint ith a table in the case here more than one relationship ith the parent table e4ists " Primary -ey constraint names sho$ld be named as SH88HM EPK 'hey can be a ma4im$m of > characters lon& I +ni3$e inde4 constraint names sho$ld be named as J E+K /hec- constraint names sho$ld be named as JE/K
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 14
PPDM Lite 1.0 - Architectural Principles
).
Desin Issues Please refer to PPDM > Architect$ral Principles doc$ment for Desi&n 1ss$es
$.
Do"ains Please refer to PPDM > Architect$ral Principles doc$ment for Domain 3$estions
'.
*nits of Measure Please refer to PPDM > Architect$ral Principles doc$ment for +nits of Meas$re 3$estions
+.
Coordinates Please refer to PPDM > Architect$ral Principles doc$ment for /oordinate 3$estions
,.
tensibilit# And !ub-settin 54tensibility and s$bsettin& refers to the ability of an individ$al data model $ser to c$stomi;e the model to meet their individ$al member b$siness needs 1n some cases# e4tensions are needed to mana&e proprietary or application specific information
,.1
becti&e 'o assist in the implementation of e4tensions or s$bsets so that chan&es to the model are easily identified to e4ternal $sers of the model (ie application vendors)
,.2
;uidelines 9 Additional tables and col$mns may be added b$t the Primary Keys and =orei&n Keys of PPDM Lite tables m$st remain intact 2 'ables and col$mns may be s$bsetted b$t the Primary Keys and =orei&n Keys of remainin& PPDM Lite tables m$st remain intact
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 1
PPDM Lite 1.0 - Architectural Principles ,.2.1 %a"in of tensions Ta*les" A total of I characters are available for namin& private table name e4tensions 'hese characters sho$ld be added as a prefi4 to the $s$al table namin& str$ct$re
54ample0 •
AB/ 1L /MPA8H ants to e4tend the model by addin& a table containin& special core analysis information 'hey o$ld name the table A(+ /%5EA8ALHS1S here ABE identifies this a private e4tension
.oluns" a total of > characters are available for namin& private col$mn name e4tensions 'hese characters sho$ld be added as a prefi4 to the $s$al col$mn namin& str$ct$re
54ample0 •
10.
AB/ 1L /MPA8H ants to e4tend the model by addin& a col$mn containin& proprietary Seismic line information 'hey o$ld name the col$mn A(+ P%P%15'A%HE=15LD here ABE identifies this a private e4tension
Meta Tables and Meta Data Please refer to PPDM > Architect$ral Principles doc$ment for PPDM Meta 'ables and Meta Data 3$estions
PPDM# P$blic Petrole$m Data Model Association# All %i&hts %eserved
Pa*e 1/
PPDM Lite 1.0 - Architectural Principles
11.
Taret Deli&erables
11.1
Docu"entation PPDM delivers the folloin& doc$mentation0
11.2
•
Data Dia&rams# shoin& PK components# =K relationships
•
'able report# a formatted report containin& definitions and field descriptions for all tables and col$mns
•
Mappin& beteen PPDM > and PPDM Lite 9 release
DDL PPDM delivers the folloin& DDL components0 •
'able creation
•
/onstraint creation (Primary# =orei&n# chec-)
•
+nits of meas$re (+M) and ori&inal $nits of meas$re (+M) forei&n -ey constraints a&ainst the meta model
•
'able synonyms for shared $se in 3$ery development
•
/ol$mn and 'able comment creation
•
1nde4 creation (note that PPDM provides inde4es for all PK and =K components: local DBA t$nin& ill be needed to ens$re the correct ones are retained)
