Database Definition Standard
This standard provides a framework of database coding for SRT. Teams involved in database designing, database coding can use this framework.
· Character set: ASCII
3 Object name definition
· The name contains all lowercases alphabet, and some special characters, “_”
· The name length is limited in 20 characters
· All the names should start at several lowercases stand for type, than a hyphen, than the true name. For example, td_name means that it is a table and it is a dynamic table.
All tables’ names should start with “t”, then followed by another lowercase stands for if the table is a static one or dynamic one.
ts_ Static table. The table is read-only. Such as the type table.
td_ Dynamic table. The table can be modified.
tb_ Big-amount table. In the future, the table will contain huge records. e.g. products table
For example: ts_type, td_name, tb_product
All views’ names should start with “v”, then followed by another lowercase stands for its type.
Do we need a number stand for how many table joined construct a view (TBD)
vs_ Static view. The view is read-only.
vd_ Dynamic view. The view can be modified. But such view should be used carefully, because it may bring some conflicts.
For example: vs_member.
All fields’ names should start with “f”, and then followed by other two lowercases. The first lowercase stand for if it is a PK, unique index, or index.
First lowercase Meaning
p Primary key field. A table can only have one PK. But more than one field can build A PK.
u Unique index field. A table can have more than one unique index. And A unique index can have more than one field.
f Foreign key
c Common field
Second lowercase Meaning
c char, varchar, char2, varchar2
i any forms of integer
b boolean, bit
m money, currency
d datetime, time, date
For example: fpc_memberid, fid_opendate
3.4 Stored procedure
All stored procedures’ names should start with “p”, followed by a lowercase stands for if it is used to query or transacts.
pq_ Used for querying data
pt_ Used for transacting
All triggers’ names should start with “g_”, followed by the table name, then followed by “_”, then the type of the trigger.
for example: g_name_insert
All triggers’ names should start with “i_”.
There are other objects in database, but such objects are not accessed by database coding, so we just let them be.
4 Script Definition
Reserved words should be uppercases, and user defined name should be lowercases.
SELECT fcc_name FROM td_Members
Indent size is 4.
4.3.1 Cut the sentence
When the sentence is too long, you can use more than one line to express it. However, the following line should be indented.
SELECT fcc_name, fpi_id FROM td_Member
WHERE fcc_name = “Ron”
4.3.2 Flow control
To describe the flow control well, we should put some indentations in the right places.
IF (condition) than
INSERT INTO td_member(fcc_ame) VALUES(“Ron”)