High Performance Database Design

1. High PerformanceDatabase Design Getting the most out of your database Chris franz Systems consultant / advantage evangelist May 2011

2. AGENDA • Design Foundations • Normalization • Naming Conventions • Documentation • Server-Side Functionality • Stored Procedures • Views • Triggers • Tips and Tricks

3. Design foundations

4. DESIGNING TABLE STRUCTURE • Determine the information you need • Organize the information into logical groups • Eliminate redundant items • Determine data types • Identify primary keys • Consider index options

5. DATABASE NORMALIZATION • Relational databases are set based • Normal forms • Third Normal Form is considered fully normalized • Higher normal forms ( BNCF, 4th, 5th) are used for better relational performance • Normal forms are cumulative • De-Normalization sometimes brings better performance

6. RELATIONAL TABLE STRUCTURE • Key relationships • Primary Key • Foreign Key • Types of relationships • One to One • One to Many • Many to Many

7. DATABASE TABLE STRUCTURE 1 ∞ 1 ∞ ∞ 1 ∞ 1 ∞ 1

8. NAMING CONVENTIONS • Be Consistent • Use Descriptive Names • Samples • CamelCase • Singular vs. plural table names • Spaces in object names

9. DOCUMENTATION • Naming Conventions a Great Help • Use the Database Object Description Property • Create a Standard Documentation Format

10. PRIMARY KEYS • Natural Keys • SSN • Part Number • Surrogate Keys • AutoInc • GUID (NewIDString) • Algorithm • Replication Considerations

11. SERVER-SIDE FUNCTIONALITY

12. CONSTRAINTS • Provide database level checks to maintain data integrity • Types • Table level • Field level • Common constraints • Null valid • Minimum value • Maximum value • Default value

13. REFERENTIAL INTEGRITY • Maintains data integrity between related tables • Types • Restrict • Cascade • Set Default • Set Null • Rules are enforced on these operations • Updates • Deletes

14. DATABASE VIEWS • What is a View • Virtual or logical table comprised of the result set of a query • Typical Uses • Display fields in a specific format • Joining tables • Aggregating data • Query on a Query • Reporting

15. TRIGGERS • Triggers are a piece of code or an SQL script that runs on the server in response to an update, insert, or delete operation • Trigger types • BEFORE: fired before the operation • AFTER: fired after the operation • INSTEAD OF: fired before the operation and replaces the operation • ON CONFLICT: raised when a conflict is detected when using replication • Fired once per row affected

16. TRIGGER USES • Data Validation • Archiving of Data • Calculating Results • Auditing Changes • Enhanced Security

17. STORED PROCEDURES • Stored Procedures are logic which runs on the server when requested by the client • Stored Procedure types • SQL Scripts • Compiled external libraries (Advantage Extended Procedures) • Input and Output parameters defined by the developer • Stored Procedures can be called from any 32-bit client

18. STORED PROCEDURE USES • Filtering Data • Calculating Results • Manipulating Records • Administrative Functions

19. Demonstration Updating a static cursor with triggers

20. DICTIONARY LINKS • Data dictionary links provide access to tables in other data dictionaries. • Direct path links • SELECT * FROM employees a, “\\server\share\hr.add”.employees b WHERE a.employee_id = b.employee_id • Authentication to linked dictionary is done using same User ID and Password as the current dictionary • Alias links using dot notation • UPDATE customers SET address = (SELECT address FROM backup.customers b WHERE b.cust_id = customers.cust_id ) • Authentication to linked dictionary can be done with same User ID and Password or via User Name & Password specified when creating the Alias

21. DICTIONARY LINK USAGE • Modification of information in other databases • Useful for storing less volatile data • Domain Tables • Reference Tables

22. REPLICATION • Terminology • Publisher – primary source of data • Subscriber – target for replicated data • Article – item to be replicated • Pull Replication • Subscriber sends a request to the publisher for changes since last request • Push Replication • Publisher tracks changes and sends the changes immediately to all available subscribers

23. One-Way Two - Way REPLICATION STRATEGIES

24. Forwarding makes the subscriber push changes from the publisher REPLICATION FORWARDING B C A

25. MULTIPLE TWO-WAY REPLICATION

26. SPOKE AND HUB

27. HANDLING REPLICATION CONFLICTS • Default • Overwrite the record during the replication • Business Logic within Trigger • Use a timestamp and apply the most recent changes • Overwrite key fields from the publisher and keep other changes intact • User Resolution • Write conflicting records to a separate table • Allow users to select which records to overwrite • Allow users to update field by field

28. Tips and tricks

29. QUICK TIPS • User Interface • Use field description property as tool tip • Use indexes (seeks) for quick lookups • Use server callback functionality to provide feedback to users • Data Dictionary Links • Use a separate dictionary for non-volatile data • Update multiple databases

30. PERFORMANCE TIPS • Use the SQL Execution Plan • Helps identify index needs • Useful for architecting statements • Use Query Logging • Identifies common SQL statements • Identifies non-optimized SQL statements

31. SECURITY ISSUES • User access • Determine how users will access the data • Determine what types of users you will need • Assign all permissions to groups • Assign users to appropriate groups • Data security • Encryption of all files • Encrypted communications • User access controls

32. SECURITY ISSUES--CONTINUED-- • Limit Data Access Using Views • Create Views to organize data for users (i.e. table joins) • Use triggers to allow for updating of views when necessary • Use Triggers and Stored Procedures • Triggers and Stored Procedures run with Admin permissions • Users do not need permissions to tables

33. SUMMARY • Database Foundations • Server-Side Processing • Tips and Tricks