wiki:Developer/KHIS2/DatabaseLayoutDeveloper

KHIS 2 Proposed Database Structure

Entities

  • Primary Entities
    • Organisations
    • Contacts
    • Resources
    • Projects
  • Secondary Entities
    • Accounts
    • Events
    • Documents
    • Collections
    • Contact_Users
    • Finance Records

  • Link Entities
    • Project_Contacts
    • Project_Documents, Contact_Documents, Organisation_Documents, Account_Documents, etc.

Entity Structure

Primary entities should be fully formed, with support for all the below options. Secondary entities should support an appropriate subset of options. Link entities are primarily link tables, but may support some of the options.

The following are options that may be supported by entities:

Entity / Entity Details separation
The main entity table is a shell, pointing to a detail record. Updates involve creating a new detail record and updating the pointer in the shell record. The detail record should be kept to essential data, with other values kept as attributes.
Entity Attributes
There is a linked list of attributes attached to the entity, along with an attribute types table. This allows new attributes to be defined dynamically in data rather than with schema changes.
Entity Account Attributes
Attributes can be marked as account linked. In this case, they are kept in a second list table, with an additional foreign key of accountid. This allows separate accounts to have different values for the same account key.
Entity Relational Integrity
Primary relationships between tables within an entity definition should have relationship constraints defined.
Entity Schema
Each entity should have a similarly named schema. Stored procedures associated with the entity should be stored in these. Tables should be stored in the appropriate data schema.
Archiving
Entities should support archiving, after which any further operations using the entity should fail.
Audit Tables
Where appropriate, entity tables should have a corresponding audit table that records insert, update and delete operations on that table.
Security
Entities should have a table recording access rights for each entity instance. There should be two utility functions to return the following:
  • whether a user has access to a particular entity.
  • a list of entities that a user has access to.
Stored Procedures
There should be appropriate Add, Update, Delete, Archive, Get and Search stored procedures defined for the options supported by the entity. These stored procedures should always take @pMyContact and @pMyAccount as parameters, and check that the user/account has suitable security access.

DB Schemas

All database elements should be created in an appropriate schema (NOT dbo!).

Some schemas will be purely for data, some purely functionality, and others may be hybrids that support both.

Schema Tables Stored Procedures Description
data YES NO Stores core entity data tables.
descr YES NO Stores description tables that describe the core data.
audit YES NO Stores core data audit tables.
config YES YES Stores tables that configure how the data is presented by the application.
access YES YES Stores tables and functionality that control access to the core data.
utility YES YES Contains miscellaneous tables and functionality for batch and maintenance processes.
contact NO YES Contains functionality for manipulating contacts.
org NO YES Contains functionality for manipulating organisations.
project NO YES Contains functionality for manipulating projects.
...
Last modified 2 years ago Last modified on 17 Sep 2015 10:06:34