|
| ICCM.DB.010 - Logical Database Design |
| OMG = Oracle Method Guideline | ||
| OMS = Oracle Method Standard | ||
 |
| General |
| OMS-61518: | When a secondary access element or secondary access controlled element (SAC) is deleted, this should be recorded in the notes of the primary access element or Primary access controlled (PAC) element. |
| Rationale | Analysts or developers might want to know what happened to the SAC. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all. |
| Example | The deletion of an attribute is recorded in the notes of the entity. | |
| OMS-61537: | Document any deviations of the CDM Standards in the respective Notes, including the OMS Standard code and the reason. |
| Rationale | Documenting these deviations helps your team members understand the decisions that have been made. This is also valuable information for anyone performing QA on the project. | |
 |
Note: If you deviate from any OMS standards on a project level, do not include the deviation for every object. If there is only one Application System involved, then document these deviations in the Description property of the Application System. When there are more Application Systems involved, then it is recommended to document the deviations in a separate document. |
|
| Application Systems |
| Name | |
 |
| OMS-61605: | Include an application code of 3 alphanumeric characters in the application name, in one of the following ways: 1. make the application name equal to the application code 2. append the application code between brackets at the end 3. start with the application code and extend it with a version number 4. start with the application code and extend it with an underscore followed by a logical name |
| Rationale | The application code is used frequently in naming conventions for objects in the application system. Because names always have a limited length, it is
useful to have a short code. As a lot of those objects are created in the database, it is necessary to use alphanumeric characters only. The application code is also proposed as a prefix for message codes (see OMG-10040). Having a fixed length of 3, makes it easy to automatically check the naming conventions and makes the message codes consistent with the format used by the Oracle tools. The three possible formats are in such a way that the application code can be pulled out of the application name programmatically, for use in utilities. |
| Example | Method 1: HSD Method 2: HEADSTART(HSD) or HSDEMO (HSD) Method 3: HSD60 Method 4: HSD_TEST |
|
| Notes | |
|
| OMS-61517: | Record the deletion of primary objects such as entities, modules, etc. as revision history in the Notes of the Application System. Add new entries on top. |
| Rationale | Analysts or developers might want to know what happened to the object. If this kind of information is not recorded, the person that can explain, might
not be available on the project, if at all. Putting new entries on top gives quick insight into the latest change. |
| Example | REVISION HISTORY Label Date Name Description 1.1 12-11-1999 S. Muller Removed function 31 and module ASP0050F 1.0 06-07-1998 L. Jellema Removed entity STATUS 1.1 ---- Removed function 31 and module ASP0050F because their functionality was dropped as result of tighter planning. 1.0 ---- Removed Entity STATUS, functionality now in domain STATUS |
|
|
| Table Definitions |
| Name | |
|
| OMS-42100: | Prefix table names with the application code. |
| Rationale | In this way, naming conflicts with database objects from other applications are avoided. In addition, it is immediately visible to which application a certain database object belongs. |
| Example | Oracle Applications consist of many different application modules. The application code for General Ledger is GL, and the application code for Accounts
Receivables is AR. In both application modules there are tables containing lookup values, namely GL_LOOKUPS and AR_LOOKUPS. These tables are created in separate schemas, but all tables are granted to one application user, often called APPS, which again has synonyms defined for the granted objects. If the application code had not been used in this situation, there would have been a naming conflict. Also by using the application code, it is immediately visible which table belongs to which module. |
|
|
| Columns |
| Name | |
|
| OMS-42251: | Do not use the table/view alias as a prefix in column names, except for discriminator columns (suffixed by _TYPE) and columns that are in a recursive relationship (also known as pig's ear). |
| Rationale | Using the table/view alias as a prefix would be superfluous as column names should always be prefixed by the table/view alias when used in SQL statements. | |
 |
Reference: View the PL/SQL standards for more details. |
| OMS-42553: | Do not repeat the table/view name in the column name. |
| Rationale | Since a column only makes sense in the context of a table/view it is not necessary to include the table/view name. When using column names in PL/SQL the standard is to always use the table/view alias as follows [alias].[column_name]. Putting the table/view name in the column name is therefor an unnecessary repetition that should be avoided. | |
| OMS-60071: | Do not start column names with P_. |
| Rationale | The Form Generator confuses such a column with parameters and will fail with an error message. | |
| OMS-61600: | Only use alphanumeric characters and '_' in column names. |
| Rationale | Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data. | |
| OMS-61606: | Do not use Oracle reserved words as column names. |
| Rationale | Reserved words are not allowed as column names in the database server. |
| Example | Do not create a column called SELECT | |
|
Attention: Oracle 8 reserved words are: |
| ACCESS | ADD | ALL | ALTER |
| AND | ANY | AS | ASC |
| AUDIT | BETWEEN | BY | CHAR |
| CHECK | CLUSTER | COLUMN | COMMENT |
| COMPRESS | CONNECT | CREATE | CURRENT |
| DATE | DECIMAL | DEFAULT | DELETE |
| DESC | DISTINCT | DROP | ELSE |
| EXCLUSIVE | EXISTS | FILE | FLOAT |
| FOR | FROM | GRANT | GROUP |
| HAVING | IDENTIFIED | IMMEDIATE | IN |
| INCREMENT | INDEX | INITIAL | INSERT |
| INTEGER | INTERSECT | INTO | IS |
| LEVEL | LIKE | LOCK | LONG |
| MAXEXTENTS | MINUS | MODE | MODIFY |
| NETWORK | NOAUDIT | NOCOMPRESS | NOT |
| NOWAIT | NULL | NUMBER | OF |
| OFFLINE | ON | ONLINE | OPTION |
| OR | ORDER | PCTFREE | PRIOR |
| PRIVILEGES | PUBLIC | RAW | RENAME |
| RESOURCE | TRIGGER | REVOKE | ROW |
| ROWID | ROWNUM | ROWS | SELECT |
| SESSION | SET | SHARE | SIZE |
| SMALLINT | START | SUCCESSFUL | SYNONYM |
| SYSDATE | TABLE | THEN | TO |
| UID | UNION | UNIQUE | UPDATE |
| USER | VALIDATE | VALUES | VARCHAR |
| VARCHAR2 | VIEW | WHENEVER | WHERE |
| Sequence in Table | |
|
| OMS-42270: | Use the following ordering sequence for table columns: 1. primary key columns 2. unique key columns 3. foreign key columns If spacing is an issue, then order the rest of the columns as follows: 4. all other mandatory columns 5. all other optional columns For clarity, it is sometimes better to keep columns together, for example, BEGIN_DATE (mandatory) and END_DATE (optional). |
| Rationale | In this way, optional columns end up at the end of a row not taking any space in the database. This also makes it easier to understand and verify the structure and completeness of the table in terms of columns. | |
| Domain | |
|
| OMS-60034: | For view columns that are based on a table column, if the underlying column is in a domain, the view column should be in the same domain. |
| Rationale | This prevents other values from being entered that are allowed by the table column. | |
| Datatype | |
|
| OMS-42271: | Only use the datatypes
|
| Rationale | The datatype CHAR may lead to coding errors when comparing values in PL/SQL code, and a CHAR(1) column does not use less space than a VARCHAR2(1)
column. The other datatypes not mentioned either have no corresponding attribute format (such as DECIMAL and FLOAT) or are mapped by the server to a different internal datatype (such as DECIMAL, IMAGE). When no maximum length and precision are specified INTEGER and SMALLINT default to NUMBER(38), REAL defaults to FLOAT(63), DOUBLE PRECISION to FLOAT(126), FLOAT defaults to FLOAT(12) and NUMBER defaults to NUMBER. To prevent unattended mappings in this matter, NUMBER columns with a maximum length precision specified should be used instead. |
|
|
Note: The CLOB datatype is sensitive to NLS settings, and the BLOB datatype is not. |
| OMS-61563: | In the case of Oracle7, do not define more than one column of the table to have the datatype LONG or LONG RAW. In the case of Oracle8, do not use LONG or LONG RAW. Use CLOB and BLOB instead. |
| Rationale | You cannot create a table in the server that has more than one LONG (RAW) column. LONG and LONG RAW have become obsolete with Oracle8. Instead CLOB and BLOB should be used. There can be more than one column with these datatypes for one table. | |
| OMS-61575: | When the column of a View is 1:1 based on a base column, the property Datatype of the view column must be equal to that of the base column. |
| Rationale | Discrepancies might lead to unwanted behavior (a different datatype might lead to an implicit time consuming datatype conversion) and probably an inconsistent look and feel. | |
| Maximum Length | |
|
| OMS-42273: | Define the Maximum Length for number columns. |
| Rationale | Number columns with no length will be 38 long in the database. This is often more than required. | |
| OMS-61566: | Set the maximum length of a column to the maximum length of the corresponding attribute. |
| Rationale | Otherwise, a discrepancy between analysis and design is introduced. | |
| OMS-61576: | When the column of a View is 1:1 based on a base column, the property Maximum Length of the view column must be equal to that of the base column. |
| Rationale | Discrepancies might lead to unwanted behavior (a different maximum length allows the user to enter data that is longer than the data that can be stored) and probably an inconsistent look and feel. | |
| Decimal Places | |
|
| OMS-42274: | Define the number of decimal places necessary for number columns. |
| Rationale | Not specifying this number leads to a floating decimal separator. | |
| OMS-61567: | Set the decimal places of a column to the decimal places of the corresponding attribute. |
| Rationale | Otherwise, a discrepancy between analysis and design is introduced. | |
| OMS-61577: | When the column of a View is 1:1 based on a base column, the property Decimal Places of the view column must be equal to that of the base column. |
| Rationale | Discrepancies might lead to unwanted behavior. A different value for the decimal places allows or restricts the user to enter decimal places where the actual column does not have the respective decimal places. In addition, it will probably lead to an inconsistent look and feel. | |
| Optional ? | |
|
| OMS-61532: | Define discriminator columns indicating a subtype as mandatory. |
| Rationale | If a discriminator column allows null values, it is not possible to determine to which type a record belongs that has the null value. Discriminator columns should ensure that every record is classified as one of the types it discriminates between. | |
| Default Value | |
|
| OMS-61568: | Set the default value of a column to the default value of the corresponding attribute. |
| Rationale | Otherwise, a discrepancy between analysis and design is introduced. | |
|
Exception: If the column represents a subtype attribute which has a default value, the column should not get a default value but the default should be implemented by a business rule which applies only to that specific subtype. |
|
| Primary Keys |
| Name | |
|
| OMS-42113: | Name primary key constraints using the convention [application code]_[table/view alias]_PK |
| Rationale | This prevents naming conflicts on the server. In addition it makes it easy to determine what kind of constraint it is, and to which application and table it belongs. |
| Example | AIF_LOC_PK AIF -- application code LOC -- table/view alias PK -- indicates the primary key constraint |
|
| Enable ? | |
|
| OMS-45012: | For tables, set the Enabled property to Yes. |
| Rationale | Only in this way the primary key constraint will be enforced. | |
|
| Unique Keys |
| Name | |
|
| OMS-42112: | Name unique key constraints using the convention [application code]_[table/view alias]_UK# where # is a sequence number starting at 1, added to the constraint name to make it unique in case there are more unique key constraints defined for the same table. |
| Rationale | This prevents naming conflicts on the server. It also makes it easy to determine what kind of constraint it is, and to which application and table it belongs. |
| Example | AIF_LOC_UK2 AIF -- application code LOC -- table alias UK2 -- indicates unique key constraint and sequence number |
|
| Enable ? | |
|
| OMS-45014: | For tables, set the Enabled property to Yes. |
| Rationale | Only in this way the unique key constraint will be enforced. | |
|
| Foreign Keys |
| Name | |
|
| OMS-45019: | Name foreign key constraints using the convention [application code]_[table/view alias]_[ref table/view alias](_[logical name])_FK(_CG) where ref table/view is the referenced table or view and the logical name is required when there is more than one foreign key constraint defined for the same referenced table or view. Use the same logical name as for the foreign key columns. Suffix foreign key constraints that are only introduced for forms generation (that is, when they should not be created at server level), with _CG. This includes foreign keys of views. |
| Rationale | This makes it easy to determine what kind of constraint it is, to which application and table it belongs, and which is the joined table. The logical
name makes the foreign key constraint unique and should make it easier to see which relationship the foreign key constraint represents. Suffixing it with _CG makes (automated) checking of the properties Complete?, Enable? and Validate In easier. |
| Example |
| AIF_LOC_PRS_FK | |
| AIF | Application code |
| LOC | Table alias |
| PRS | Table alias of joined table |
| FK | Indicates foreign key constraint |
| AIF_CTR_ADR_BILL_FK AIF_CTR_ADR_SHIP_FK |
|
| AIF | Application code |
| CTR | Table alias |
| ARD | Table alias of joined table |
| BILL SHIP |
Indicates Bill To relationship, Indicates Ship To relationship |
| FK | Indicates foreign key constraint |
| ADR_ID_BILL ADR_ID_SHIP | Name of foreign key columns, see OMS-60013 |
| Enable ? | |
|
| OMS-45016: | For foreign keys without the _CG suffix, set the Enable? property to Yes. |
| Rationale | Only in this way the foreign key constraint will be enforced in the database. Foreign keys with _CG suffix are for Client Generation only (see also OMS-45019) and do not need to be enforced in the database, so the Enable? property does not matter for them. | |
|
| Check Constraints |
| Name | |
|
| OMS-42115: | Name check constraints using the convention [application code]_[table/view alias]_[logical name] where the logical name is an indication of what the check constraint is about. |
| Rationale | This makes it easy to determine what kind of constraint it is, and to which application and table it belongs. |
| Example |
| AIF_LOC_CHK_DATE | |
| AIF | application code |
| LOC | table alias |
| CHK_DATE | logical name |
| OMS_ACT_9_DIGITS | |
| OMS | application code |
| ACT | table alias |
| 9_DIGITS | logical name |
|
| Database Triggers |
| Name | |
|
| OMS-61620: | Name an INSTEAD OF trigger using the convention [application code]_[view alias]_IO . |
| Rationale | This makes it visible to which application and view the trigger belongs. |
| Example | HSD_V_EMP_IO | |
| PL/SQL Definition | |
|
| OMS-45024: | The name of the PL/SQL definition for a trigger should be the same as the name of the trigger itself. |
| Rationale | This makes it easier to quickly locate the belonging trigger module in the repository. | |
|
| Sequence Definitions |
| Name | |
|
| OMS-42353: | Name a sequence that generates a value for exactly one column in a table using the convention [application code]_[table/view alias]_SEQ# where # is a sequence number starting at 1, added to the sequence name to make it unique in case there are more sequences defined for the same table. |
| Rationale | This prevents naming conflicts and makes it easy to determine which application and table use the sequence. |
| Example | OMS_CUS_SEQ1 | |