Architectural Principles Tables
Contents |
Structure
Naming of tables should reflect the subject area, sub-area or natural grouping of tables. The structure of a table names is: SUBJECT AREA + MODIFIER1(sub-area) + MODIFIER2(grouping)... Example:
- WELL
- WELL_PRESSURE
- WELL_PRESSURE_AOF
- WELL_PRESSURE_AOF_4PT
Structure Rules
- Maximum of 24 characters is used for PPDM table names.
- Table names are singular and in present tense.
- Intersection / associate tables will be named according to business usage and by borrowing from the names of the intersecting tables.
- Example:
- WELL_TEST
- Cross-reference tables created from a single parent table are named by adding an XREF qualifier to the name of the table.
- Example:
- BA_XREF
Name Component Rules
- Names must only contain alphanumeric characters and underscore.
- Names are not case sensitive.
- Complete names must not be in the Oracle, Sybase or PL SQL reserved words list (see Appendix A for a list of these words)
- Avoid using 'A', 'AN', 'AND', 'OF', 'OR', 'THE'.
Consistency
- Use consistent subject area abbreviations.
- Example:
- BA - Business Associate
- R_ - Reference Tables
- WELL_DIR_SURVEY - Well Directional Survey
- SEIS_ - Seismic
- Use common acronyms or industry accepted abbreviations where useful and meaningful to the membership.
- Example:
- AOF - Absolute Open Flow
- GOR - Gas Oil Ratio
- DLS - Dominion Land Survey
Naming Process
- Construct business name (fully descriptive).
- Choose appropriate subject area name.
- Choose appropriate modifier(s).
- Determine if name meets maximum length specifications (24 characters for table names)
- If too long, apply abbreviations
- Be consistent. Naming abbreviations or class words that have been used in past should be re-used when the context is similar. Remember that the objective is to ensure the model is as consistent and easy to understand as possible
- It may be difficult to arrive at a name that pleases everyone in the work group. Use the Table comments to clarify the meaning and usage of the table.
Synonyms
Each table is assigned a name and a synonym in the CASE tool. Synonym names are released as an optional segment of the DDL in a file names *.SYN. Synonyms are named so as to group tables into logical components. For example, reference table synonyms are prefixed R_. Primary key and foreign key names are based on the synonyms.
Table comments
- Every table must have a complete description explaining what it is.
- Table long names always appear as the first part of the table comments (in upper case), followed by a colon (:).
- Example:
- AIR DRILLING INTERVAL: The Air Drilling Interval table contains depth interval and air volume information where air drilling was utilized in a wellbore. Air drilling is rotary drilling that uses compressed air instead of a circulating mud system.
Table length
Some relational databases impose a limit on the maximum size of a table. SQL Server 2000 limits the total allowed length to 8060 bytes. Even though you can successfully create a table in SQL Server 2000 that could theoretically contain more than 8060 bytes, you will be unable to actually add or update a record with more than 8060 bytes of data.
Table design must allow entry level SQL92 compliant relational database implementations to achieve 100% Gold level compliance; however, SQL Server is not entry level compliant. Despite this, the designers have chosen to support this limitation as this RDBMS is sometimes desired by implemenation teams for other reasons. For this reason, the total length of a table may not exceed 8060 bytes.
SQL Server limitations are posted here - http://msdn2.microsoft.com/en-us/library/ms143432.aspx
SQL Server 2008 has lifted this restriction - http://technet.microsoft.com/en-us/library/ms178158.aspx
A discussion about this limitation is on the forums, you must be a current member and logged into the website to view - http://www.ppdm.org/forums/viewtopic.php?t=304
Table deprecation
From time to time, work group reviews and re-engineering the model will make some portions of the model invalid. While every attempt will be made to ensure that redundancy is cleared up during design, there are times when existing tables are left in the model for a period of time so that members can plan a migration. Note that this practice is only used when absolutely essential, and should be rare.
Tables that are included in a model release but scheduled for deprecation in the next release will be named with a Z_ prefix. New implementations of the PPDM Models should avoid using these tables, and existing implementation should be revised to ensure that these tables removed from use. Mappings from deprecated tables to new table functionality will be provided with the model release. Tables named Z_% in a previous release will be removed from the next model release.
