This section provides information about the table and column naming standards in the OFSAA data model.
Topics:
· Table Naming Standards and Conventions
· Column Naming Standards, Conventions, Data Size, and Information
· PK and FK Naming Conventions
This section provides information about the Table naming standards and conventions in the OFSAA data model.
Topics:
These table naming standards must be followed in the OFSAA data model:
· Entities in the OFSAA application subject area must be in Uppercase.
· Entities in the Staging subject area must be in Lowercase.
· Underscores separate the distinct terms that define the Table or Column as per the standard conventions.
· Table names must start with the application name. For example, FSI_, REV_, and so on.
· All table names must be in plural form.
· Table names must have indicators of the functional area.
· Table prefix and suffix must be compatible with the table type.
· Table names must have only A to Z, 0 to 9, and underscore (_) characters.
· Table names can have multiple underscores.
For more information, see the Table Naming Conventions section.
A particular category of tables must contain the prefix and suffix standards mentioned in the following sections.
· Naming Conventions in the Staging and Reporting Tables
· Naming Conventions in the Application Processing Tables
This section contains information about the prefix standards that must be followed in the Staging and Reporting tables.
|
Class of table |
Prefix |
|---|---|
|
Dimensions |
DIM_ |
|
Stage |
STG_ |
|
Reporting Table |
FCT_ |
This section contains information about the prefix and suffix standards that must be followed in the Application Processing tables.
|
Class of table |
Prefix |
Suffix |
Contains |
|---|---|---|---|
|
Codes |
FSI_ |
_CD |
|
|
Instrument or Data |
FSI_D_ |
|
|
|
Output facts |
FSI_O_ |
|
|
|
Intermediate |
FSI_ |
|
_INTERMEDIATE |
|
History |
FSI_ |
|
_HIST |
|
Logs or Error |
FSI_ |
|
_LOG _ERRORS |
|
Audit Trail |
FSI_ |
|
_AUDIT |
|
Translated Tables |
FSI_ |
_B, _TL, _CD, _MLS |
|
|
Other |
FSI_ |
|
|
|
Temporary Tables |
FSI_ |
|
_TEMP |
|
View |
FSI_ |
_V |
|
|
Template Tables |
FSI_D_ |
_TEMPLATE |
|
|
Interface Tables |
|
_INTF |
|
This section provides information about the Column naming standards and conventions, data size, data type, and column information in the OFSAA data model.
Topics:
This section provides information about the OFSAA data model table column naming standards.
Topics:
· Generic
The generic standards for the column naming are as follows:
· Column names must contain only A to Z, 0 to 9, and underscore (_) characters.
· Column names can contain multiple underscores.
· The column name must not be very generic. Avoid words such as term, multiplier, description, name, code, and so on.
· Column names must not be cryptic and must be intuitive. Use long names instead of short unclear abbreviations.
The naming standards for the Staging and Reporting columns are as follows:
· Columns related to staging entities must be in Lower case.
· Column names must have the type of the column as part of the prefix or suffix.
· Column names of staging entities must have the prefix. v_ for varchar, n_ for numeric, d_ for the date, F_ for flag columns.
The naming standards for the Application Processing columns are as follows:
· Columns related to OFSAA application entities must be in the Upper case.
· Column names in OFSAA application entities must have a suffix. For example, _CD for code type columns, _FLAG for flag type columns, _BAL for balance type columns, and so on.
· The class of columns must be suffixed to the column name.
This section provides the suffixes standards that must be a part of the column names depending on the column type.
|
Class of Column |
Suffix |
|---|---|
|
System Generated ID |
_SYS_ID or _ID |
|
Code |
_CD or _CODE |
|
Name |
_NAME |
|
Description |
_DESC |
|
Flag |
_FLAG or _FLG or _IND |
|
Percentage |
_PCT |
|
Rate |
_RATE |
|
Balance |
_BAL/BALANCE |
|
Amount |
_AMT/AMOUNT |
|
Term |
_TERM |
|
Multiplier |
_MULT |
|
Frequency |
_FREQ |
A column that is created in the entity can belong to various categories and it must belong to one of the following domains. A category can contain one or more data sizes.
The following table contains Domain and Column details.
|
Domain Name |
Domain Description |
Data Type |
Column Length and Precision |
Column Prefix |
|---|---|---|---|---|
|
AMOUNT |
Monetary value |
NUMBER |
22,3 |
N_ |
|
Amount_Long |
Monetary value |
NUMBER |
30,11 |
N_ |
|
Amount_Long_Type2 |
Monetary value |
NUMBER |
22,6 |
N_ |
|
Amount_Medium |
Monetary value |
NUMBER |
25,6 |
N_ |
|
BALANCE |
Monetary Balance |
NUMBER |
14,2 |
N_ |
|
Blob |
|
BLOB |
|
|
|
Business_Domain |
|
VARCHAR2 |
65 |
V_ |
|
Capital |
Monetary value |
NUMBER |
22,6 |
N_ |
|
Capital_Long |
Monetary value |
NUMBER |
38,3 |
N_ |
|
Capital_Long_Type2 |
Monetary value |
NUMBER |
38,6 |
N_ |
|
Capital_Long_Type3 |
Monetary value |
NUMBER |
38,15 |
N_ |
|
CHAR |
One character data |
CHAR |
1 |
V_ |
|
CHAR_RANGE |
Character Range |
CHAR |
18 |
V_ |
|
CLOB |
|
CLOB |
|
|
|
CODE |
Defined Alpha or Numeric Code value |
NUMBER |
5 |
N_ |
|
Code_AlphaNumeric |
Defined Alpha or Numeric Code value |
VARCHAR2 |
25 |
V_ |
|
Code_Alphanumeric_Long |
Defined Alpha or Numeric Code value |
VARCHAR2 |
20 |
V_ |
|
Code_Alphanumeric_Long_Type2 |
Defined Alpha or Numeric Code value |
VARCHAR2 |
40 |
V_ |
|
Code_Alphanumeric_Long_Type3 |
Defined Alpha or Numeric Code value |
VARCHAR2 |
50 |
V_ |
|
Code_Alphanumeric_Medium |
Defined Alpha or Numeric Code value |
VARCHAR2 |
10 |
V_ |
|
Code_Alphanumeric_Medium_Type2 |
Defined Alpha or Numeric Code value |
VARCHAR2 |
15 |
V_ |
|
Code_Alphanumeric_Short |
Defined Alpha or Numeric Code value |
VARCHAR2 |
5 |
V_ |
|
Code_Alphanumeric_Short_Type2 |
Defined Alpha or Numeric Code value |
VARCHAR2 |
6 |
V_ |
|
Code_Currency |
Currency code value |
VARCHAR2 |
3 |
V_ |
|
Code_Long |
Defined Alpha or Numeric Code value |
VARCHAR2 |
60 |
V_ |
|
CODE_NUM |
Undefined Numeric Code Value |
NUMBER |
5,0 |
N_ |
|
Code_Numeric_Long |
Numeric Code value |
NUMBER |
20 |
N_ |
|
Code_Numeric_Medium |
Numeric Code value |
NUMBER |
10 |
N_ |
|
CODE_NUMERIC_SHORT |
Numeric Code value |
NUMBER |
5 |
N_ |
|
Code_Short |
Defined Alpha or Numeric Code value |
VARCHAR2 |
3 |
V_ |
|
DATE |
Date value |
DATE |
|
D_ |
|
Datetime |
Date and time value |
DATE |
|
D_ |
|
DESCRIPTION |
Description |
VARCHAR2 |
255 |
V_ |
|
Description_Medium |
Description |
VARCHAR2 |
40 |
V_ |
|
DEFAULT |
Default Data type |
|
|
|
|
Flag |
True or False value |
CHAR |
1 |
F_ |
|
Flag_Boolean |
|
NUMBER |
1 |
N_ |
|
Flag_Identifier |
|
NUMBER |
2 |
N_ |
|
FREQ |
A recurring period of time |
NUMBER |
5,0 |
N_ |
|
FREQUENCY |
A recurring period of time |
NUMBER |
5 |
N_ |
|
ID_NUMBER |
Reserved |
NUMBER |
14 |
N_ |
|
Identifier |
Reserved |
VARCHAR2 |
20 |
V_ |
|
IDENTITY |
Reserved |
NUMBER |
10,0 |
N_ |
|
Indicator |
|
VARCHAR2 |
1 |
V_ |
|
LEAF |
Leaf column |
NUMBER |
14 |
N_ |
|
LEDGER_BALANCE |
Monetary value |
NUMBER |
15,4 |
N_ |
|
LONG_RATE |
Long Rate |
NUMBER |
15,9 |
N_ |
|
LONG_RAW |
Variable-length binary data |
CHAR |
18 |
V_ |
|
MULT |
Multiplier |
CHAR |
1 |
V_ |
|
N/A |
Not applicable |
CHAR |
18 |
V_ |
|
Number |
Numerical value |
NUMBER |
22 |
N_ |
|
NUMBER |
Numerical value |
NUMBER |
22,3 |
N_ |
|
Number_Amount |
Monetary value |
NUMBER |
22,6 |
N_ |
|
Number_Days |
Numerical value |
NUMBER |
10,3 |
N_ |
|
NUMBER_FACTOR |
Numerical value |
NUMBER |
11,6 |
N_ |
|
NUMBER_FACTOR_MEDIUM |
Numerical value |
NUMBER |
30,3 |
N_ |
|
Number_Generic |
Numerical value |
NUMBER |
22 |
N_ |
|
NUMBER_GENERIC_TYPE2 |
Numerical value |
NUMBER |
11,8 |
N_ |
|
Number_Long |
Numerical value |
NUMBER |
20 |
N_ |
|
Number_Medium |
Numerical value |
NUMBER |
10 |
N_ |
|
Number_Percentage |
Numerical value |
NUMBER |
10,5 |
N_ |
|
Number_Short |
Numerical value |
NUMBER |
5 |
N_ |
|
Number_Term |
Numerical value |
NUMBER |
8,4 |
N_ |
|
Number_Value |
Numerical value |
NUMBER |
22,3 |
N_ |
|
NUMERIC_TYPE2 |
Numerical value |
NUMBER |
10,0 |
N_ |
|
NUMBER_TYPE1 |
Numerical value |
NUMBER |
|
N_ |
|
NUMBER_LONG_TYPE2 |
Numerical value |
NUMBER |
30 |
N_ |
|
Numeric |
Numerical value |
NUMBER |
10 |
N_ |
|
PCT |
Percent value |
NUMBER |
8,4 |
N_ |
|
Percent |
Percent value |
NUMBER |
8,4 |
N_ |
|
Percent_Long |
Percent value |
NUMBER |
15,11 |
N_ |
|
Phone_Fax_Number |
Phone or Fax number |
NUMBER |
15 |
N_ |
|
Probability_Model |
Ratio value |
NUMBER |
7,4 |
N_ |
|
Probability_Score |
Ratio value |
NUMBER |
11,6 |
N_ |
|
RATE |
All types of rates |
NUMBER |
8,4 |
N_ |
|
Rate |
All types of rates |
NUMBER |
11,6 |
N_ |
|
Rate_Long |
Long Rate value |
NUMBER |
30,11 |
N_ |
|
Ratio |
Ratio value |
NUMBER |
9,6 |
N_ |
|
Score |
|
NUMBER |
5 |
N_ |
|
Short_Rate |
Short Rate value |
NUMBER |
8,4 |
N_ |
|
ShortName |
Description |
VARCHAR2 |
100 |
V_ |
|
String |
|
VARCHAR2 |
20 |
V_ |
|
SurrogateKey_Long |
Application number and Run number |
NUMBER |
10 |
N_ |
|
SurrogateKey_Long_Type2 |
Application number and Run number |
NUMBER |
15 |
N_ |
|
SurrogateKey_Medium |
Application number and Run number |
NUMBER |
5 |
N_ |
|
SurrogateKey_Short |
Application number and Run number |
NUMBER |
3 |
N_ |
|
SWITCH |
Switch value |
CHAR |
18 |
V_ |
|
SYS_ID_NUM |
Identifies application ID |
NUMBER |
10 |
N_ |
|
SYSTEM_IDENTIFIER |
Identifies application ID |
NUMBER |
10 |
N_ |
|
SYSTEM_IDENTIFIER_LONG |
Identifies application ID |
NUMBER |
25 |
N_ |
|
System_Identifier_Medium |
Identifies application ID |
NUMBER |
15 |
N_ |
|
Term |
Non-recurring period of time |
NUMBER |
5 |
N_ |
|
Text_Comments |
Description |
VARCHAR2 |
500 |
V_ |
|
Text_Comments_Type2 |
Description |
VARCHAR2 |
1000 |
V_ |
|
Text_Expression |
Description |
VARCHAR2 |
4000 |
V_ |
|
Text_Long_Description |
Description |
VARCHAR2 |
100 |
V_ |
|
Text_Long_Description_Type2 |
Description |
VARCHAR2 |
120 |
V_ |
|
Text_Long_Description_Type3 |
Description |
VARCHAR2 |
150 |
V_ |
|
Text_Medium_Description |
Description |
VARCHAR2 |
60 |
V_ |
|
Text_Medium_Description_Type2 |
Description |
VARCHAR2 |
40 |
V_ |
|
Text_Message |
Description |
VARCHAR2 |
2000 |
V_ |
|
Text_Short_Description |
Description |
VARCHAR2 |
30 |
V_ |
|
Timestamp |
Date and time with seconds |
TIMESTAMP |
|
D_ |
|
TIMESTAMP_TYPE2 |
Date and time with seconds |
TIMESTAMP |
6 |
D_ |
|
UNDEFINED |
Column data type not defined |
CHAR |
18 |
V_ |
|
Varchar_Generic |
Variable-length alpha-numeric value |
VARCHAR2 |
20 |
V_ |
|
VARCHAR2 |
Variable-length alpha-numeric value |
VARCHAR2 |
30 |
V_ |
The main information captured in each column is as follows:
· Column Name
· Erwin Domain (it represents the standard logical data types and maps to the physical data types)
· Column Data Type and Length
· Primary Key Indicator
· Foreign Key Indicator
· Not NULL Indicator
· Comment or Description of the Column
· User Defined Properties (UDP), if any.
The naming conventions for the Primary Key and Foreign Key are as follows:
· PK_<Table_Name>_<Sequence>
· FK_<Table_Name>_<Sequence>