Total Knowledge Projects Jobs Clientele Contact

UU Database

Personal tools
From Total Knowledge
Revision as of 08:25, 13 April 2006 by Alexeyp (Talk | contribs)
Jump to: navigation, search

Database structure requirements

Naming Conventions

  1. All tables should have names of two or three words, separated with underscore. The combination of first letters of these words should be unique in the whole database. In this document, this combination, followed with underscore ('_'), is called table name prefix. Example: teachers_list, course_group_list.
  2. Every field in every table should have table name prefix, followed by the field name. Example: teachers_list(tl_id), course_group_list(cgl_id).
  3. Every index name should start from "ndx_", followed with the table name prefix, and the field names, included in the index, separated with underscore.
  4. The primary key name should start from "pk_" followed by the table name prefix
  5. The foreign key name should start from "fk_" followed by the table name prefix and foreign table name prefix

Structure Requirements

  1. Tables in the database are placed in two domains: main and logs. The logs domain contains tables that contain access logs and statistics information. These tables may be emptied at any given moment without affecting the actual data.
  2. Every table should have a unique primary id (integer,auto-increment), logically not connected to the actual data.
  3. Any application that connects to the database, should have only read-access to the tables. All the modification operations, including insert, update, delete operations, should be performed in the stored procedures. The stored procedures should be implemented in a stored procedure language (most likely PL/SQL or equivalent), that doesn't allow to create a stored procedure without an integrity check. Integrity check should validate every object used in the stored procedure.
  4. Any transaction, performed by the application, should include only one stored procedure call.
  5. The important corner-stones tables should have triggers, logging any modifications in these tables as a set of user name, modification date and time, modification type. (If this is accepted, I'd provide the implementation details).
  6. Under no circumstances tables should use automatic cascade-delete on foreign keys. For any delete of detail records, stored procedure should verify the access rights for the given group of the records within the table (assuming that the user is granted access to the stored procedure removing records).