1. IntroductionThis document describes the standards to be used when designing and developing this database.
|
|
| |
1.1 Audience
The audience for this document will be database designers, developers, or technical leads. It may also
include developers performing design or development code reviews, or QA staff reviewing design
documentation or code.
|
| |
1.2 Scope
How to perform database design and how to write database code are outside the scope of this document.
|
| |
1.3 Responsibilities
Database application developers are responsible for producing database schema (object) creation scripts, code, schema design, and code documentation.
|
| |
2 Architecture and Design
|
| |
2.1 Users
SYSDBA account should not be used for general maintenance and upgrades. Instead the Database Owner account should be used.
|
| |
2.2 Tables
|
| |
2.2.1 Table Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for table names.
2. Table names should not normally start with a digit.
3. It is recommended that table names are restricted to 30 characters or less.
4. Table names in DDL should not be enclosed with Double Quotes (") to prevent accidental case sensitive names.
5. Where possible a Domain should be used in lieu of a Data Type when used in CREATE TABLE DDL statements.
|
| |
2.2.2 Column Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for column names.
2. Column names should not normally start with a digit.
3. It is recommended that you restrict column names to 30 characters or less.
4. No SQL language reserved words should be used as column names.
5. Column names in DDL should not be enclosed with Double Quotes (") to prevent accidental case sensitive names.
|
| |
2.3 Stored Procedures
|
| |
2.3.1 Procedure Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for column names.
2. Column names should not normally start with a digit.
3. It is recommended that you restrict column names to 30 characters or less.
4. No SQL language reserved words should be used as column names.
5. Column names in DDL should not be enclosed with Double Quotes (") to prevent accidental case sensitive names.
|
| |
2.3.2 Parameter Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for parameter names.
2. Parameter names should not normally start with a digit.
3. It is recommended that Parameter names are restricted to 30 characters or less.
4. Input Paramaters should be prefixed with IP, denoting input
5. Output Paramaters should be prefixed with OP, denoting output
|
| |
2.3.3 Variable Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for variable names.
2. Parameter names should not normally start with a digit.
3. It is recommended that variable names are restricted to 30 characters or less.
4. Variables should be prefixed with v, denoting variable
|
| |
2.4 Views
|
| |
2.4.1 View Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for View name.
2. View names should not normally start with a digit.
3. It is recommended that View names are restricted to 30 characters or less.
|
| |
2.4.2 Column Names
1. Normally, use only alphanumeric characters plus the underscore (_) character for column names.
2. Column names should not normally start with a digit.
3. It is recommended that you restrict column names to 30 characters or less.
4. No SQL language reserved words should be used as column names.
5. Column names in DDL should not be enclosed with Double Quotes (") to prevent accidental case sensitive names.
|
| |
2.5 Indexes
2.5.1. Index/constraints and performance
1. Identify primary, unique, and foreign keys (to enforce important or critical data integrity) as part of the initial design process.
2. To guard against slow queries and degrading performance all queries should have their plans analysed.
3. Indexes should be used where appropriate. A list of the SQL queries and their indexes used should also be submitted.
|
| |
2.6 BLOBs
1. If you are using significant amounts of BLOB storage, or access to BLOB columns is quite high in volume, a separate table should be used for BLOB storage.
|
| |
2.7. Interfaces
1. All database APIs must define error handling, and success/failure notification.
2. When accessing database from Java code, JDBC should be used.
3. When accessing database from .Net code, Firebird.net provider should be used.
4. When accessing databases from C++, IBPP should be used.
5. ODBC may also be used to connect to Firebird databases if desired/necessary.
6. Use connection pooling where possible/appropriate to reduce connection set up/tear down overhead for connections from client or middle tier code to server databases.
|
| |
2.8. RDBMs specific features
1. Select data types appropriate to the target RDBMS. Designs should be as RDBMS agnostic as possible: for example, minimize use of sets and abstract data types, unless these are key to the design. It is accepted that that there will inevitably be some RDBMS specific data typing.
|
| |
3 Storage
|
| |
3.1 Encryption and Checksums
|
| |
3.1.1 Encryption
1. Encryption MUST be used for storing user passwords.
|
| |
3.1.2. Checksums
1. MD5 should normally be used as the algorithm for producing a checksum if it is required to check the integrity of data at source/destination.
|
| |
4 Data Access
|
| |
4.1 Reading
1. Reading of data should be done via Stored Procedure's or Views, direct table access is discouraged.
|
| |
4.2 Writing
1. Writing of data should only be done via Stored Procedure's.
|
| |
5 Development
|
| |
5.1 Coding standards
|
| |
5.1.1 General
1. Minimal database coding standards are enforced, but database code should always aim to be clear, concise, consistent, and commented.
|
| |
5.2 Security
1. Database users should only be granted sufficient privileges to enable them to perform the tasks that they need to.
2. When connecting from web applications, where possible always connect to the database as the same user. This minimizes the chances of leaving a security hole against a particular user, and enables connection pooling techniques to be more readily taken advantage of to improve performance.
3. Plain text passwords should never be displayed in client side code.
4. Plain text passwords should not be stored on public facing servers.
|
| |
5.3 Date/Times
1. Date/Times within the database should normally be stored as GMT (Greenwich Mean Time). Any conversion required should be done at the client end of the application.
2. The TIMESTAMP datatype should be used to store Date/Time, where appropriate.
|
| |
5.4 Error handling
1. The error handling strategy should be clearly defined for all database APIs.
|
| |
5.5 Headers
1. All scripts/modules should contain a header, including a description of the main purpose of the script/module, the name of the module, the date last updated, the author (last modifier), and the version.
2. All procedures and functions should contain a header, including a description of purpose, parameters, exceptions thrown, and possible return codes.
|
| |
5.6 Cursor handling
1. All cursor resources should be released after use.
|
| |
5.7 Managing connections
1. When NOT using connection pooling, connections should always be closed after use. When using connection pooling, connections should be released back to the pool after use.
2. All database resources associated with a used connection should be explicitly released before the connection is closed or released back to the connection pool. Resources MUST be released correctly when error or exception conditions occur.
|
| |
5.8 Abstraction of interface to database
1. Connection details (server, username, password) should be stored in configuration files and not hardcoded in code. This enables database connection code to be deployed to test and operational environments without any need for code amendment (and/or recompilation).
2. Application code should make use of common (database abstraction layer) connection code to access the database. This enables application code to be insulated from changes to underlying database connection information, and for amendments to be made (e.g. to introduce/implement connection pooling)
|
| |
5.9 Maintenance
1. All schema changes MUST be made via a script, and not via an ad hoc change.
2. Any database change script that is backed out should normally be backed out with a separate database change script.
|
| |
6 Documentation
|
| |
6.1 Table, column comments
1. Every table should have a comment and description of its purpose.
2. Every column of every table should also have a column comment, explaining its purpose, default value, etc.
|
| |