|
| ICCS.DB.010 - Logical Database Design |
| OMG = Oracle Method Guideline | ||
| OMS = Oracle Method Standard | ||
 |
| General |
| OMG-10042: | Document any unusual design decisions including the reasons in the Notes property of the respective object. |
| Rationale | This helps your team members understand the decisions that have been made. It is also valuable information for anyone performing QA on the project. | |
| 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. |
|
| Logical Database Design |
| OMG-10059: | If practical, make one diagram showing all tables and foreign keys between them. |
| Rationale | To understand the logical data model it is convenient to have one diagram that shows all tables with their foreign key relationships. | |
 |
Suggestion: If the diagram becomes too large, then you may change the preferences of the diagram so that only the primary key columns or no columns at all are displayed. |
| OMG-10060: | Make separate Server Model diagrams for every subsystem |
| Rationale | To understand what data is used by what subsystem, it is convenient to have a diagram that shows all tables that are used by that subsystem with their foreign key relationships. This is especially true for large or multi-application systems. | |
|
Note: If a diagram with all tables and foreign key relations is already available, it is recommended to start from a copy of that diagram. Remove the superfluous tables and foreign key relationships, so that the basic layout is kept intact. |
|
| 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 |
|
|
| Relation Definitions |
| Name | |
|
| OMS-61601: | Only use the alphanumeric characters and '_' in the name of tables, views and snapshots. |
| 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. |
|
|
| Table Definitions |
| OMG-10075: | Use surrogate primary keys (a sequence without meaning to the end users) for all tables. |
| Rationale | Primary keys are not updateable. If you use surrogate primary keys, the end users are allowed to update any column that is meaningful to them (usually
the surrogate primary key is not even shown). Also, it avoids primary keys of more than one column. This avoids storage and coding implications of concatenated keys used as foreign keys. If, for programming reasons, you want to have a certain foreign value available in a table, you can add a denormalized foreign key which is not part of the primary key of the table. |
| Example | For the table HSD_DEPARTMENTS, the end users are only interested in the Department Name and Location, which make a department unique. The Department Id is a
surrogate key, filled by an Oracle Sequence. The id is not shown to the users, they are not interested in its value. This id is used as a foreign key for the Employees table, so that in HSD_EMPLOYEES there is a DEP_ID column identifying the Department, instead of a DEP_NAME and DEP_LOCATION column. |
|
 |
Database Design Transformer: When creating tables from entities, you can set the 'Create surrogate keys for each new table' check box in the Keys tab of the Settings dialog. If you do so, also specify that those columns should be called ID (see OMS-42257): go to the Other Settings tab and uncheck 'Surrogate key columns' and 'Columns' at the Elements for which you want prefixes generated. |
| OMS-61617: | If a table has a surrogate primary key (a sequence without meaning to the end users), the table must have at least one unique key (often referred to as the 'natural' key). |
| Rationale | A surrogate key is meaningless to the end users. Therefore they need another way to uniquely identify a row, that is they need a unique key. Otherwise, it is not possible to specify which row is to be used in a foreign key from another table. |
| Example | If the table HSD_DEPARTMENTS has a surrogate key (the ID column), the users need another unique key, for example the combination of NAME and LOCATION. Now, if the users must assign an employee to a department, they only need to specify the Department Name and Location, and the program will determine its Id. | |
| 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. |
|
| OMS-42101: | Define table names in plural. |
| Rationale | The name of the table normally indicates what is the content of the table. Tables physically contain more entries of that content, and is therefore perceived as plural. An Entity on the other hand, which is defined in singular, represents the definition of an object of importance within the business that is modeled and are therefore perceived as singular. | |
|
Database Design Transformer: When using the Database Design Transformer to create tables from entities, then the plural property of the entity is used as the table name. |
| OMS-42103: | Name tables derived from an entity using the convention [application code]_[entity plural] |
| Rationale | By using this naming convention it is immediately visible from which entity the table is derived. |
| Example | OMS_PRODUCTS OMS -- application code PRODUCTS -- entity name in plural |
|
| OMS-61560: | Name journal tables after the table with the actual data, suffixed by _JN. |
| Rationale | This is consistent with the naming convention used by Oracle Designer. Also, when using the RON, the journal table is shown directly under the table with the actual data. Journal tables can easily be recognized. In addition, it is easier to create and name journal tables by using a utility. |
| Example | QMS_EMPLOYEES has the QMS_EMPLOYEES_JN as journal table. | |
| OMS-61561: | When the name of the table exceeds 30 characters, make it shorter by removing every second and following vowels of a word. Start at the last word of the
plural and move forward, until the name is short enough. Well-known abbreviations might be used as an alternative, such as for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded in the Business Terminology. |
| Rationale | Table names cannot be longer than 30 characters. The table names are used a lot during build and therefore should be highly predictable and logical. |
| Example | MAS_EMPLOYEE_ASSIGNMENT_HISTORY becomes MAS_EMPLOYEE_ASSIGNMENT_HISTR (29 characters long) |
|
| Alias | |
|
| OMS-42104: | Define an alias for each table. |
| Rationale | Table aliases are used in SQL Statements to indicate to which table a certain column belongs. Also, the table alias is used in naming conventions for key constraints, indexes, PL/SQL Definitions, module components, etc. | |
 |
Reference: See PL/SQL standards for more details about using the alias in SQL statements. |
|
Database Design Transformer: The table alias is used by the Database Design Transformer when generating constraint names for primary, unique, and foreign keys, and when generating default indexes for the table. |
| OMS-61530: | The table alias should be exactly three alphanumeric characters, except for journal tables, which should be four. |
| Rationale | Table aliases are used in SQL Statements to indicate to which table a certain column belongs. Also the aliases are used in the naming conventions for
key constraints, indexes, PL/SQL Definitions, module components, etc. When the alias is used so heavily, the shorter the better is a good rule. Three letters is the shortest that leaves enough possibilities. For journal tables see OMS-61531. To be able to identify the table alias easily from the names of the above mentioned objects, the characters must be alphanumeric. |
|
 |
Exception: Large systems may change this standard to 4 characters (5 for journal tables), if OMS-30534 and OMS-61599 are also changed accordingly. |
| OMS-61531: | The alias of a journal table is the same as that of the table with the actual data, followed by the character J. |
| Rationale | This makes it easier to create journal tables by using a utility. |
| Example | QMS_EMPLOYEES has alias EMP, QMS_EMPLOYEES_JN has alias EMPJ. | |
| Col. Prefix | |
|
| OMS-61625: | Do not use the Column Prefix property for Tables. |
| Rationale | A column is always used in the context of its table, so the information would be superfluous. | |
| Display Title | |
|
| OMG-10026: | Enter a Display Title for the table to indicate a default. If the table maps one to one to an entity, then use the plural entity name as the Display Title. |
| Rationale | A default makes it easier to provide consistent titles throughout the Application. The entity name is meaningful for the business, and is therefore in most situations a good choice for the display title. | |
 |
Application Design Transformer: When running the Application Design Transformer after having run the Database Design Transformer, the Display
Title of the table becomes the default display title of the Module Component. However, the window title is NOT populated by this title; it is left empty. When the window title property itself is not populated, and it is not the first module component in the module, then the Forms Generator uses the module component as the window display title. When it is the first module component, and there has not been specified any window title, then the module display title is used. Only when also the display title is used, then the display title of the module component is also used for the first window title. |
| Journal | |
|
| OMS-60009: | When using journaling, set the Journal property to Server. |
| Rationale | If you use Client or Client calls server procedure, the Form Generator creates code in all related forms to populate the journal table. This behavior is undesirable, because the nature of journaling makes server-side implementation imperative. | |
|
Exception: If you want to use the Headstart Utility for Journalling, set the Journal property to None. Headstart has a different way of specifying which tables need to have which journalling. See the Headstart Utilities User Guide for more information. |
| Notes | |
|
| OMS-60070: | Revision History of a Table (including its subobjects such as columns and constraints) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself. Add new entries on the top. |
| Rationale | Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?"
are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval. Putting new entries on top gives quick insight into the latest change. |
| Example | REVISION HISTORY ================ Label Date Name Description 1.1 06-01-1999 L. Jellema Change History Columns added 1.0 06-07-1998 L. Jellema Initial Creation 1.1 ---- Added change history columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY to enable initial creation, and last update information for every record. 1.0 ---- Initial Creation |
|
|
| View Definitions |
| Name | |
|
| OMS-42201: | Define view names in plural. |
| Rationale | The name of the view normally indicates what is the content of the view. Views physically contain more entries of that content, and are therefore perceived as plural. | |
| OMS-42202: | Name views according to the following convention: [application code]_V_[logical name] |
| Rationale | This makes views easier to recognize, for example, in case of a select from one of the static dictionary views of the Oracle Server. |
| Example |
| OMS_V_CUSTOMERS | |
| OMS | Application code |
| V | View indicator |
| CUSTOMERS | Logical name in plural |
| AIF_V_ORDERS | |
| AIF | Application code |
| V | View indicator |
| ORDERS | Logical name in plural |
| Alias | |
|
| OMS-60025: | Define an alias for each view. |
| Rationale | View aliases are used in SQL Statements to indicate to which view a certain column belongs. Also, the view alias is used in naming conventions for key constraints, PL/SQL Definitions, module components, etc. | |
|
Reference: See the PL/SQL standards for more details about using an alias in SQL statements. |
| OMS-61599: | Name the view alias following the convention V_[three alphanumeric characters]. |
| Rationale | If you start the view alias with V_ it is clear that it concerns a view and you have the possibility to follow it with an existing table alias (for
example, when the view is based primarily on one table). View aliases are used in SQL Statements to indicate to which view a certain column belongs. Also the aliases are used in naming conventions for key constraints, PL/SQL Definitions, module components, etc. When the alias is used so heavily, the shorter the better is a good rule. For the last part of the alias, three characters is the shortest that leaves enough possibilities. To be able to identify the view alias easily from the names of the above mentioned objects, the characters must be alphanumeric. |
| Example | V_EMP | |
|
Exception: Large systems may change this standard to 4 characters, if OMS-30534 and OMS-61530 are also changed accordingly. |
| Col. Prefix | |
|
| OMS-42210: | Do not use a column prefix for view columns. |
| Rationale | Using the view alias as a prefix would be superfluous as column names should always be prefixed by the table alias when used in SQL statements. | |
| Comment | |
|
| OMS-42205: | Describe the purpose of the view in a maximum of 60 characters. |
| Rationale | This provides a quick understanding of the purpose of the view. This is especially useful when quickly scanning through the views within the various navigators. | |
| Description | |
|
| OMS-42212: | Define the purpose and usage in detail for the view. Use a similar template as used to document entities. |
| Rationale | This helps your team members understand what is the purpose and usage of the view. | |
| Notes | |
|
| OMS-61609: | Revision History of a View (including its subobjects such as columns and base table usages) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself. Add new entries on the top. |
| Rationale | Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?"
are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval. Putting new entries on top gives quick insight into the latest change. |
| Example | REVISION HISTORY ================ Label Date Name Description 1.1 06-01-1999 L. Jellema Change History Columns added 1.0 06-07-1998 L. Jellema Initial Creation 1.1 ---- Added change history columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY to enable initial creation, and last update information for every record. 1.0 ---- Initial Creation |
|
|
| Columns |
| Name | |
|
| OMS-42207: | Assign to columns in a view the same name as the columns in the underlying tables/views. In case of naming conflicts, include the underlying table/view alias as a column prefix. |
| Rationale | In this way, it is immediately obvious to what columns the view columns relate. This information is valuable during development and maintenance. | |
| OMS-42250: | Define column names in singular. |
| Rationale | Each column should represent singular information. If a plural is used, this indicates bad naming or design; either more columns are required or another table is required to maintain multiple entries for this information. |
| Example | If the column TELEPHONE_NUMBERS is used for a CUSTOMER, then this could either be changed into a number of columns : FAX_NUMBER, HOME_TEL_NUMBER, MOBILE_NUMBER, WORK_NUMBER, or another table may be required, like CONTACT_NUMBERS, including indicators like FAX, HOME, MOBILE etc. |
|
| 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-42252: | Keep the column names short, but still logical and self-descriptive. Columns that implement an attribute should have the same, or very similar, names as the attribute they implement. Use an underscore where the attribute contains spaces. When the name of the column exceeds 30 characters, make it shorter by removing every second and following vowels of a word. Start at the last word of the plural and move forward, until the name is short enough. As an alternative well-known abbreviations might be used, for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded in the Business Terminology. |
| Rationale | A column name may not exceed a length of 30 characters. When a column implements an attribute, using the same name as the attribute makes it easier to determine which column is the implementation of which attribute. | |
| OMS-42255: | Name discriminator columns using the naming convention [super entity short name]_TYPE |
| Rationale | This is the Database Design Transformer's default. It also makes it easier to immediately determine what is the discriminator column without checking the repository (for example, when doing a describe in SQL Plus). |
| Example | PRN_TYPE PERSON -- super-entity name PRN -- super-entity short name TYPE -- suffix indicating the use of the column |
|
| OMS-42256: | Define organization-/project-specific naming conventions for:
|
| Rationale | This ensures consistency throughout your system. |
| Example | The following naming conventions are suggested: |
| Type | Naming Convention |
| Date Columns | ..._DATE |
| Time Columns | ..._TIME |
| Code Columns | ..._CODE |
| Amount Columns | ..._AMT |
| Currency Columns | ..._CURR |
| Description Columns | ..._DESC |
| Flag Columns | ..._FLAG |
| Status Columns | ..._STATUS |
| Number Columns | ..._NUM |
| Sequence within parent columns | SEQ_IN_P |
| 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-60013: | Name foreign key columns using the convention: [ref table/view alias]_[ref table/view primary key column name] where ref table/view means the referenced table or view. When there are more foreign keys with the same referenced table and a uniqueness conflict would occur, use the following naming convention: [ref table/view alias]_[ref table/view PK column name]_[logical name] Use the same logical name as the relevant foreign key constraint name. |
| Rationale | This makes it easy to see that a column is a foreign key column, but also what the foreign key column references. In addition, it is easy to make a cross reference to the table or view referenced by the foreign key column as the name of the primary key of that table is known. This information is valuable during development and maintenance. |
| Example |
| PRN_ID | |
| OMS_PERSONS | Referenced table |
| PRN | Alias referenced table |
| ID | Primary key column referenced table |
| ADR_ID_BILL_TO and ADR_ID_SHIP_TO | |
| OMS_ADDRESSES | Referenced table |
| ADR | Alias referenced table |
| ID | Primary key column referenced table |
| BILL_TO | Indicates that it is the Bill To address |
| SHIP_TO | Indicates that it is the Ship To address |
| 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-42261: | Any column that ranges over a fixed set of predefined values should be associated with a domain that describes that set of values. |
| Rationale | This allows for reuse of the predefined values, and makes maintenance easier as whenever a value is changed, added or removed, this only has to be changed in the domain. | |
| OMS-60012: | For discriminator columns, use a domain with the discriminator values of the subtype table/entity usages ('Mapped to Entity') as the allowable values. |
| Rationale | If the allowable values do not match the discriminator values, the Generators will create the wrong code. Using a domain instead of column allowable values is covered by OMS-42404. | |
| 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. | |
| OMS-61564: | Link a column to the same domain as the corresponding attribute. When no domain has been specified for the attribute, a domain for the column may be introduced. |
| Rationale | Otherwise, a discrepancy between analysis and design is introduced. | |
| 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. | |
| OMS-61565: | Set the optional flag of a column to the optional flag of the corresponding attribute. In case of super/subtype implementation by one table the only exception that does not need to be documented is the mapping of a mandatory attribute at the subtype level to an optional column. |
| Rationale | Otherwise, a discrepancy between analysis and design is introduced. | |
| 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. |
| Display Type | |
|
| OMS-58107: | Use check box when only one value out of Yes or No is applicable, and the Yes/No statement is not contrived or obscure. |
| Rationale | In this way you ensure that it is obvious what the unchecked value means. |
| Example | Do use (x) Allow Override (x) Receipt Required Do not use (x) Male (contrived to force Male/Female to a yes/no response) Do not use (x) Root Menu (opposite of root menu isn't obvious) In each of these 'Do not use' cases, a radio group would present a more intuitive set of choices to the user. |
|
| AutoGen Type | |
|
| OMS-42282: | Only use the AutoGen Type types Date Created, Date Modified, Created By and Modified By in addition to server-side implementation when these fields need to be displayed in the generated form. When using the autogenerate field types, check the Record Time on Change History Columns check box on General Generator Options dialog to include the time. Set the Display Datatype of the associated columns to Datetime. |
| Rationale | If you use these types the Form Generator creates code in all related forms to populate the journal table. However, the nature of such data auditing makes server-side implementation, using database triggers, imperative. In case of a server side implementation data auditing also takes place when data is inserted or updated in other ways than by using a form, for example, in case of batch modules. | |
|
Reference: Refer to the CDM Standards and Guidelines Library, Volume 2, Design and Generation of Multi-Tier Applications, Chapter "Logical Database Design", in the section entitled, "Data Auditing", for more information. |
| OMS-61533: | When using an AutoGen type, then set Server Derived to Yes. |
| Rationale | Derivation is by nature something done by the server. The derivation also takes place when data is inserted or updated in other ways than by using a form, for example, in the case of batch modules. | |
| Source Attribute | |
|
| OMS-61570: | For each column that has a relationship with a source attribute, this relationship must be recorded. |
| Rationale | When the definition of an attribute changes, these changes must be propagated to the associated column(s) and visa versa. Recording the relationship makes this easier. Normally columns without a relationship with an attribute are introduced for technical reasons. | |
| Validation Error Message | |
|
| OMS-61538: | Do not record the actual message text in the validation Failure Message. Instead use a message code that refers to the message text in a message table. |
| Rationale | This offers you the flexibility to easily change message text online and implement multi-lingual messaging. | |
 |
Form Generator: If the column has a domain with allowable values, and you use a text item (with LOV) to represent the column, this message code is used when the user enters an invalid value. |
 |
Generation: The Headstart Template Package provides a complete messaging environment. See the Headstart Template Package User Guide for more details. |
| Hint | |
|
| OMS-42299: | Do not include the allowable values for an item in the hint text. |
| Rationale | The hint text of a column is used as the default for the hint text of a corresponding item, and OMS-61628 says the same about item hint texts. | |
|
Attention: Where the column is derived from an attribute, the hint text is inherited from the Attribute Comments. Change the hint text, if necessary. |
| Description | |
|
| OMS-42278: | Describe the purpose of the column if this is not clear from the column name itself. |
| Rationale | This makes it easier to all team members to understand what the column is about. | |
| Where/Validation Condition | |
|
| OMS-45010: | Do not use the Where/Validation Expression on the column level. Instead, define any Where/Validation Expression on the table level. |
| Rationale | A validation that can be entered here, can be entered by using a check constraint. You should record your validations consistently throughout the application (that means as a check constraint). Also the where/validation condition on column level is ignored by the Server generator. A check constraint is not. | |
| Notes | |
|
| OMS-60014: | Columns that are optional and where a null value has a different meaning than value unknown, should have a short column note that explains the meaning of a null value occurring for that column. |
| Rationale | This prevents misinterpretation of data. | |
 |
Warning: Do not confuse this with the Null Value property. The Null Value property indicates which value should be stored if the column has no value. This is meant for non-Oracle databases. Note: This value is not used by any generators. |
|
| 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 |