Oracle Database Development conventions

L Naming rules 1, Table attribute Specification1.1 table name for each subsystem, the table name prefix is composed of the abbreviations of each part plus. The naming conventions for each part are as follows: The con _ data table name must consist of words or abbreviations with feature meanings, and "_" is no longer used in the middle, for example, sys_tablename.The prefix. Eng _The table defined by the data center is prefixed DTC _The table defined by the collaboration platform is prefixed FRM _The business data table is prefixed Biz _Is the prefix. 1.2 The partition name prefix of the table is P _. The partition name must contain words or strings with specific meanings. For example, the partition p_2004100101 of sys_tablename indicates that the partition stores data for 2004100101 time periods. 1.3 The field name must start with a letter and use a word or abbreviation with a feature meaning. The field name is no longer separated. 1.4 The primary key name prefix is pk _. The primary key name should be a field name consisting of prefix + Table name +. If the composite primary key has many fields, it only contains the first field. You can remove the prefix from the table name. 1.5 The foreign key name prefix is FK _. The foreign key name should be the field name consisting of the prefix, foreign key table name, primary key table name, and foreign key table. You can remove the prefix from the table name. 2Index2.1 The prefix of a common index is idx _. The index name should be a field name consisting of the prefix, table name, and table name. If the composite index contains many fields, only the first field is included and the serial number is added. You can remove the prefix from the table name. 2.2 The primary key index prefix is idx_pk _. The index name should be the primary key field name consisting of the prefix + Table name +. When creating a table, use using index to specify the primary key index attribute. 2.3 The unique index prefix is idx_uk _. The index name should be a field name consisting of the prefix, table name, and table name. 2.4 The foreign key index prefix is idx_fk _. The index name should be a foreign key field name consisting of a prefix + Table name +. 2.5 The prefix of the function index is idx_func _. The index name should be a feature expression character consisting of prefix + Table name +. 3ViewPrefix: V _ <abbreviation of subsystem> _. Name the view by business operation. 4Stored ProcedurePrefix: PRC _ <abbreviation of subsystem> _. Name a stored procedure according to business operations. 5TriggerThe prefix is trig _. The trigger name should be prefix + Table name + trigger name. 6FunctionPrefix: Fun _. Name a function by business operation. 7Data PacketsThe prefix is PKG _. Name a data packet based on the service operation set. 8SequencePrefix: seq _ <abbreviation of subsystem> _. Name by service attribute. 9Tablespace9.1 The prefix of the common tablespace is TBS _. The name is based on the storage features, such as tbs_infomation. 9.2 dedicated tablespace TBS _ <Table Name> _ nn. The tablespace stores data from a specified table or Several partitions of a table. Nn = indicates different partitions of a table. 10Data Files<Tablespace Name> NN. DBF. Nn = 1, 2, 3, 4 ,... . 11Common variablesThe prefix is VaR _. Contains characters, numbers, and date variables. 12Cursor variableThe prefix is cur _. Store the cursor record set. 13Record VariablesThe prefix is rec _. Stores record-type data. L Name 1LanguageEnglish words should be used for naming, especially Chinese characters. Chinese or special characters are not allowed for naming. When an object with duplicate names is of different types, the type prefix or suffix is added to indicate the difference. 2Case sensitivityAll names are capitalized to facilitate migration of different databases and avoid Program Calling problems. 3Reserved WordsSQL reserved words cannot be used for naming. 4Name LengthThe table name, field name, and view name must be within 20 characters (including the prefix ). L Data Type 1Character TypeChar is used for fixed-length strings, and varchar2 is used for unfixed-length strings. Avoid using the char type when the length is not fixed. If the preceding conditions occur during data migration, the trim () function must be used to intercept the space after the string. 2Number TypeNumber type is recommended for numeric fields. The integer format is number (n), and the format containing decimal places is number (m, n ). 3Date and Time3.1 The date type of the database is preferred for system time generated by the database, such as date. 3.2 The Date and Time types generated by data import or external applications use varchar2, and the data format is "yyyymmdd hh24: MI: SS ". Avoid using large fields (blob, clob, long, text, image, etc.) unless otherwise required ). 3.4 unique keys use series sequence whenever possible For numeric unique key values. L Design ToolsThe Erwin 4.1 design tool is used to design the physical model. All database objects should be designed on the physical model as much as possible, and each physical model should have a corresponding text description. All changes to database objects are based on the physical database model. To avoid character-sensitive issues, the scripts generated use uppercase/lowercase letters as the standard.