Database Principles for Online Test Software
When using RDBMS, one must have some coding standards where everyone in the organisation understands. The same Table in the database can be read by any team and has the same meaning. Similarly, with the column, every column should be meaningful and well understood by testers, developers and support team.
Primary Key, Foreign Key, Unique Key, Constraint Rule should have similar coding nations which are well defined and understood by the organisation.
Table Naming Convention
Table name should be no more 25 characters.
The table should represent a single row.
The table should be lower case and the words are delimited by underscore ‘_’
Defined business abbreviation carefully, maybe no more than three-letter like TLA.
The abbreviation should be used at the beginning of the table to distinguish from any other tables in the organisation. Similar to the namespace concept.
Column Naming Convention
The column should be in the lower case
Any words in the column should be separated by the underscore ‘_’
Any column should no more than 40 characters.
All column name should have special prefix example for index (i_) foreign key (fk_) or unique key (uq_).
Any constraint rule should have prefix appropriate to the organisation.
The composite key should be well defined and ck_ as a prefix to identify its a composite key.
A unique nullable primary key must be defined.
Datatype
Datatype plays a major role in the application, they should strictly match to the application data type. For example, if a boolean is defined then datatype should be 0 or 1 and well transformed to Boolean through hibernate, JPA or any persistence architecture.
A foreign key must have the same datatype as Primary Key.
All alphanumeric must be a VARCHAR, VARCHAR2 or TEXT depending on the size.
Use the appropriate datatype
tiny int
short
int
double
decimal(x,y)
binary
Date and Timestamp must be distinguished and ensure it's not null. There must be default date like 1–1–1980 etc depending on the system.
Timezone value should be incorporated for multiple zones. In some cases, UTC must be used (Universal Time Code)
Please visit Business Integration Software to see various products using databases especially Exam Software.
Assessment Management Software
Multiple Choice Question Software
