Target of development:
  • To improve EAV data model and support data transformation from arbitrary classical row model based on Walker’s scheme into CUEAV and back out. This supports capability to create changeless User Interface for implementing CRUD operations and gives ability to add new entities without additional rebuilding user forms and what is more we can observe real time value deviation monitoring because of Attribute table has guideline values.
  • Also this model makes it possible to get out of EAV main imperfection – complexity and low processing power of analytical queries.

Sheme1.jpg

  • The future development of CUEAV.
Most convenient CUEAV data model application will be made use of for creating automatically customized interface according updated entities for example in clinical research DB.
It is necessary to take notice of that it is enough 3 modified CUEAV’s tables: Class, Attribute, ClassLinks and some additional triggers so that to keep up execution CRUD operations in row classical DB and herewith there is no necessity changing user’s interface. Suitable T-SQL script will be downloaded in the near future.

Sheme3.jpg

Brian Walker. A surrogate key architecture.
http://searchsqlserver.techtarget.com/feature/A-surrogate-key-architecture-to-perform-powerful-SQL-Server-database-operations
The rules for this proposed architecture are quite simple :
1. Every table has a primary key.
2. The primary key is a single column.
3. The primary key is the first column.
4. The primary key column is named to correspond with the table name.
5. The primary key migrates to child tables as a foreign key with the same characteristics.
6. The primary key column is numeric.
7. The primary key column is a 4-byte integer data type.
8. The primary key column uses the IDENTITY property (starting at 1 and incrementing by 1).
CUEAV additional rules:
9. Key names are generated as Table name & ID.
10. Column name of the table is not ended as ID.
11. Tables name removed into CUEAV must be launched with prefix.

6 freeware stored procedures written by Brian Walker are used in CUEAV : sp_CreateForeignKeyIndexes, sp_CreateForeignKeys, sp_CreatePrimaryKeys, sp_DeleteForeignKeyIndexes, sp_DeleteForeignKeys, sp_DeletePrimaryKeys.

Last edited Jun 14, 2013 at 2:24 PM by S_Dumansky, version 23