1 / 34

High Performance Database Design

High Performance Database Design. Getting the most out of your database. Chris franz Systems consultant / advantage evangelist. May 2011. AGENDA. Design Foundations Normalization Naming Conventions Documentation Server-Side Functionality Stored Procedures Views Triggers

Download Presentation

High Performance Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related