Naming rules for Oracle databases

Data library usage rules
Terms:
Site)
Websites already established or to be established by the company, such as the Chinese site (China), the international site (Alibaba), and the Chinese site (China.
OLPS
Online Process system, an online processing system, that is, the database connected to the website that our members can browse, is what we call the frontend.
BOPs
Back office process system: the background processing system, that is, the publishing system of our website.
BzO
Business Object (?), The company's website's low-level Java object is mainly responsible for dealing with databases.
I. Naming Conventions
1. Naming Conventions for databases and database objects such as table, sequence, procedure, and column;
2. Name meaningful English words. Except for some commonly used words (see the list), you must avoid using abbreviations. Words are separated by hyphens;
3. Except for the database name with a length of 1-8 characters, the remaining names must be 1-30 characters and the database link name must not exceed 30 characters;
4. Only English letters, numbers, and underscores can be used for naming;
5. Avoid using reserved Oracle words such as level and keywords such as type (see the list );
6. The names of related columns should be the same as possible;
7. database Name: the online database name is "OLPS" + 2-4 characters indicating the site (the same as below), and the background database name is "BOPS" + 2-4 characters indicating the site. The test database is named "OLPS | BOPS" + "test", and the Development database is named "OLPS | BOPS" + "test". The usage mode is schema/user) to distinguish different sites.
8. Index name: table_name + column_name + index_type (1 byte) + idx. Each part is separated by an underscore. The column name composed of multiple words. It takes the first few letters and the last word to form column_name. For example, index in the sample table member_id: unique index in the title of sample_mid_idx news table: news_titile_uidx;
9. Sequence name: seq _ + table_name;
Ii. Notes
1. This note is mainly used for PL/SQL Program And other SQL files, other for reference;
2. There are three annotations accepted by sqlplus:
-Here is a comment.
/* Here is the comment */
Rem here is the comment
3. Start annotation, similar to the start annotation in javak, which mainly lists the file name, write date, copyright description, program functions and modification records:
Rem
Rem $ header: filename, version, created date, auther
Rem
Rem Copyright
Rem
Rem Function
Rem function explanation
Rem
Rem notes
Rem
Rem modified (yy/MM/DD)
Rem who when-for what, recently goes first
4. Block comments, such as table comments and procedure comments, are the same as Java:
/*
* This table is for trustpass
* Mainly store the information
* Of trustpass members
*/
5. Single Row comments, such as column comments:
Login_id varchar2 (32) not null, -- member ID

Iii. indent
A low-level statement after a high-level statement is generally Indented by four spaces:
Declare
V_memberid varchar2 (32 ),
Begin
Select admin_member_id into v_memberid
From Company
Where id = 10;
Dbms_output.put_line (v_memberid );
End;

indentation of different parts of the same statement. If it is sub statement, it is usually two spaces. If it is closely related to a part of the previous sentence, it is reduced to its alignment:
begin
for v_tmprec in
(select login_id,
gmt_created, -- Here indented as column above
satus
from member -- sub statement
where site = 'China'
and Country = 'cn ')
loop
NULL;
end loop;
end;
4. Broken Line
• A line cannot exceed 80 characters
• spaces after commas between different words in the same statement
• Other delimiters leading space
select offer_name
| ','
| offer_count as offer_category,
id
from Category
where super_category_id_1 = 0;
5. Case sensitivity
it is the keyword size of Oracle, and the table name and column name are in lower case.
6. Select a column type
• Replace the Boolean value with Char (1).
• Replace varchar2 with Char as much as possible;
• varchar (2) can contain a maximum of 4000 characters.
• Date is accurate to microseconds rather than days.
• Use clob instead of long and blob instead of long raw;
• Oracle has only one data type, so the length must be specified.
7. primary key selection
select meaningful, columns that are not too long and can uniquely identify record rows are used as primary keys. If such columns do not exist, sequence is used as primary keys.
8. Select the column length
select the column length as needed. There is a corresponding web page, and the length of the corresponding column on the page is consistent.

In addition to database implementation, verify data at the presentation layer as much as possible.
9. SQL statement conventions
1. Avoid using SQL statements in cycles whenever possible.
2. Avoid applying functions to columns in the WHERE clause:
Select *
From service_promotion
Where to_char (gmt_modified, 'yyyy-mm-dd ')
= '2017-09-01 ';
Instead, use:
Select *
From service_promotion
Where gmt_modified
> = To_date ('2017-9-01 ', 'yyyy-mm-dd ')
And gmt_modified
<To_date ('2014-9-02 ', 'yyyy-mm-dd ');

3. Avoid using the automatic type conversion function of the database:
Select * from category
Where id = '000000'; -- ID's type is number
4. Avoid invalid connections:
Select count (*)
From offer a, count_by_email B
Where a. Email (+) = B. Email;
5. Alias used for join:
Select a. *, B. offer_count (*)
From offer a, count_by_email B
Where a. Email (+) = B. Email;
6. Get the meta information of the table:
Select *
From table_name
Where rowid is null (or rownum = 1 or PK = impossible_value );
Consider the following:
Select *
From table_name
Where 1 = 0;

10. Conventions on the Alibaba table
If Alibaba BzO is used, the table must have at least the following fields:
Site varchar2 (32)
Gmt_create date
Gmt_modified date
If it is inherited from idbizobject, there is a number-type column, usually a primary key, which corresponds to a sequencecf; if it is inherited from stridbizobject, there is a varchar2 column.
11. Column value conventions
1. Columns with fixed value lists, such as status, action, and site, whose values are in lower case;
2. Select numbers as much as possible to represent the values in the fixed value list. The corresponding columns are defined as number type;
3. Use char (1) to indicate the upper case of the Boolean value: "Y", "n ".
12. Definition of duplicate table attributes
Duplicate attribute Columns cannot be selected;

For repeated attribute columns that can be selected multiple times, id_varray should be used instead of the query columns that do not need to be used as the query columns. If the number of repeated columns is large, another table should be created, if the number of columns is small and the column value is one of the two values, the number type should be used to add bits.
XIII. data modification conventions
If data in the production environment is found to be incorrect and needs to be corrected, the data update form should be submitted on RA terminal, and the database operator should finish the modification 17 hours before that day.

The structure changes and data updates of database update programs are similar.

If you need to prepare data or update the structure for submitting the sqtt test, write the data to the test request. Then, the sqtt Comrade mail (indicating the link to the test request form) modifies the database through the database operator. After a new form is added to the Intranet team, the test request is submitted separately.
Iv. Database Design Process
The DBA should attend the new project design stage meeting. Encoding can be started only after the schema is determined.

Abbreviation list:
Payment pymt
My trade activity MTA

Special Word List:
Access decimal initial on Start
Add not insert online successful
All default integer option Synonym
Alter Delete Intersect or sysdate
And DESC into order table
Any distinct is pctfree then
As drop level prior
ASC else like privileges trigger
Audit exclusive lock public uid
Between exists long raw Union
By file maxextents rename unique
From float minus resource update
Char for mlslabel revoke user
Check share mode row validate
Cluster grant modify rowid values
Column group noaudit rownum varchar
Comment having nocompress rows varchar2
Compress identified Nowait select View
Connect immediate null session whenever
Create in number set where
Current increment of size
Date index offline smallint

Char varhcar varchar2 number date long
Clob blob bfile
Integer decimal
Sum count grouping average
Type