Attribute Name Data Type Maximum Length Optional <ENTITY NAME (without Class Word)> KEY e.g. LAND USE CODE primary key attribute is LAND USE KEY Number (preferred) Or Varchar2 Number(10) should be used with a surrogate key Or Varchar2 No CREATE USER NAME Varchar2 30 No CREATE TS Date No SHORT NAME Varchar2 If primary key is Number No LONG NAME Varchar2 No DESCRIPTION Varchar2 No BEGIN DATE Date No END DATE Date Yes MODIFY USER NAME Varchar2 30 Yes MODIFY TS Date Yes Table 2 – Code Entity Attributes SHORT NAME If a surrogate key is used, this attribute stores a shortened version of the full name of the code value for use in drop-down fields. LONG NAME Used to hold the fully expanded term of the code value. DESCRIPTION Used to hold the full business description of each code value. Table 3 – Code Entity Attribute Definitions Processing Entity Attributes – TEMP and RPT The following attributes must be included in TEMP and RPT processing entities. Attribute Name Data Type Maximum Length Optional <ENTITY> KEY e.g. PAYMENT TRACKING RPT primary key attribute is PAYMENT TRACKING RPT KEY TEMP TABLE DATA primary key attribute is TEMP TABLE DATA KEY Number Number(10) should be used with a surrogate key No
The Relationship Name phrases must not overlap and must be sized to view the entire phrase. Entity Formatting Entities must be formatted and colored for ease of readability. Entity Type Fill Data – An entity that will become a data table. A data entity contains the application’s core attributes. The resulting transactional table will be one to which the users input and/or retrieve data from on a regular basis. pale yellow Code – An entity that will become a code table. The entity will contain repeatedly used data values that fall within the management of the application administrative team. A code entity often populates the List of Value (LOV) fields in the application and is used for data validation. A code entity must be named using the “CODE” Entity Class Word, for example “PAY CODE”. light blue Processing – An entity that will become an audit, report, or temporary table. The entity will contain data stored for audit purposes or that is reloaded daily, weekly, or on demand for some business function. light peach Table 6 – Entity Colors Logical Model Diagram Entity Relationship Placement For diagrams to be legible the entity relationship lines should be placed as cleanly as possible. The preferred line placement is top to bottom (crow’s feet pointing down). If this is not possible in all cases then an optional placement is with lines from left to right (crow’s feet pointing to the right). A simple example of a Logical Model Diagram is displayed on the next page. The following recommendations are offered in order of precedence. Readability is the goal. Whenever possible, place entities so that the relationship lines do not cross an entity box. Place entities to avoid crowding relationship names so that the name is in white space. Place related entities near each other to avoid relationship lines crossing the entire diagram. It is better for a line to cross an entity than to use bent lines over a lot of space. Avoid crossing relationship lines if possible. Point crow’s feet to the right (East) and/or down (South). In other words, the “three point prongs” point to the right and/or down. Minimize the use of bent relationship lines. Straight lines are preferred but bent lines are acceptable to avoid crossing entities and relationship lines. Bent lines are not acceptable if they travel from one side of the diagram to the other.
Number NBR number varchar2 A (numeric, alphanumeric, or combination) assigned to an entity occurrence to uniquely identify it. This class should only be used when the associated data element is widely known and accepted with the suffix of "Number". The use of qualifier words is urged to increase clarity. The class suffix should not be used by itself. CONTRACT NBR EMPLOYEE NBR FILE NBR DIVISION NBR DRIVER LICENSE NBR PROCESSING NBR TOTAL_NBR NUM OF CONTRACT EMPLOYEE NUMBER FILE NO DIVISION NUM DRIVER LICENSE NO PROCESSING NUMBER NUMBER Percent PCT number A ratio between two data values (with number data types) expressed as a percentage. The use of qualifier words is suggested to remove ambiguity. The class suffix should not be used by itself. COMPLETE PCT COMPLIANCE PCT SALARY COMMISSION PCT SURCHARGE PCT WATER VAPOR PCT TOTAL_PCT COMPLETE PERCENTAGE COMPLIANCE PERCENT PCT SALARY COMMISSION PCT SURCHARGE WATER VAPOR PERCENT PERCENT or PERCENTAGE Rate RATE number A value, which usually remains constant over time, which can be used for calculation or computational purposes. The use of qualifier words is strongly recommended to remove ambiguity. The class suffix should not be used by itself. ANNUAL USAGE RATE DIRECT LABOR RATE FIXED RATE FLOW RATE OVERHEAD RATE TAX RATE TOTAL_RATE ANNUAL USAGE RT RATE DIRECT LABOR FIXED RT FLOW RT RATE OF OVERHEAD TAX RT RT Text TEXT varchar2 A group of sentences that captures knowledge about a business event or situation that uses a narrative style. Typically these attributes are free style text, contain more than one sentence, and provide an alternative class for Descriptions where this class is most natural from a business point of view. The use of qualifier words is highly encouraged to remove uncertainty. The class word should not be used by itself. COMMENT TEXT ENFORCE ACTION TEXT ERROR TEXT FULL TEXT EVENT NARRATIVE TEXT INCIDENT TEXT MESSAGE TEXT DOCUMENT_TEXT COMMENT TXT ENFORCE ACTION TX TEXT FOR ERROR FULL TXT TEXT EVENT NARRATIVE TEXT OF INCIDENT TEXT MESSAGE TXT or TX
Time TIME timestamp A specific point within a 24 hour period at which an event has or will occur. Use the TIMESTAMP data type to keep the most precise time to the millisecond, including time zone. If the portion of importance is the time, use the Time class word. If the date and exact time is relevant, use the Timestamp class. In order to remove ambiguity, use qualifier words. The class word should not be used by itself. ACTIVITY START TIME OPERATING TIME START TIME STORE CLOSE TIME STORE OPEN TIME CURRENT_TIME ACTIVITY TM OPERATING TM TIME OF START TIME STORE CLOSE TM STORE OPEN TM Timestamp TS timestamp The combination of date and time used to capture a specific date and point in time. This class is primarily used for audit column purposes. The abbreviated class word suffix must not be used by itself and requires the use of one or more qualifier words. CREATE TS END TS ENTRY TS LAST UPDATE TS MODIFY TS INCIDENT TS CREATE TIMESTAMP END TIME STAMP TS ENTRY LAST UPDATE TIMESTAMP MODIFIED TIME STAMP TIME STAMP FOR INCIDENT TS or TIMESTAMP