Database Design for DNN Developers
This comprehensive guide covers the role of the database in DotNetNuke (DNN) development, including data design, performance improvement, SQL code structuring, security considerations, and code management. Explore the importance of database decisions in scaling, performance, and security, along with comparisons between simple data stores, relational databases, and NoSQL data stores. Learn about DNN Core and Module Templates for CRUD operations, benefits of using an RDBMS, and strategies for handling varying data schemas. Dive into database computation engines, problem-solving techniques, and more.
Uploaded on Feb 20, 2025 | 0 Views
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Database Design for DNN Developers Sebastian Leupold
Sebastian Leupold Microsoft MVP dnnWerk Verbund Deutschsprachige DNN-Usergroup DNN Localization Team German Translation of DNN
Agenda Role of the Database in DNN Data Design Improve Performance Structuring SQL Code for Re-Use Security Concerns Code Management Sample
Role of the Database in DNN Data Store is an important decision: - Scaling - Performance - Security - Alternatives Simple Data Stores Relational Databases (RDBMS) NoSQL Datastores Database as Computation Engine Database as Consistent Data Container
Role of the Database in DNN /1 DNN Core and Module Templates provide CRUD i.e. Using the Database like a Fixed Length File Few Exceptions (GetUsersAdvancedSearch) Few NoSQL Attempts Few uses of XML in the core and 3rdParty
Role of the Database in DNN /2 Benefits of an RDBMS - Data Types (optimal storage of data) - Unique and Check constraints (Validation) - Indexes (Performance) - Relations (Integrity, Joins) - Optimized for Mass Operations - Computation Engine - Self Optimizing (Table Statistics, Query Plan)
Role of the Database in DNN /3 Problem handling varying data schemas - Structure not available on Install - and/or - - Structure varies by object instance Abuse: UserProfile.ExtendedPermission Storing multiple values in a single text column Benefit no extra table needed Store structured objects with a single call Disadvantage Not search/filter support by the database No Protection against faulty data
Role of the Database in DNN /4 Handling varying data schemas Serializing Data (FnL) XML data type (with Schema) Abuse Text Store and ignore RDBMS features Using NoSQL Data Store
Role of the Database in DNN /5 Database as Computation Engine Complete Implementation as Business layer Using Stored Procedures, Transactions, Triggers Advantages Fast Data Processing Always Consistent Data Downside not optimal for strings and step by step computing Difficult error handling
Role of the Database in DNN /6 Database as Consistent Data Container Implementing Rules enforcing Integrity: Check Constraints ForeignKey References Access using complete Stored Procedures Documentation Naming Conventions Descriptions Developer Doc
Data Design Modelling the Use Case Using ORM (EF): Code First or Database First? Tables and Relations Normalization Using Proper Data Types (avoid old Text and nText) Default and Check Constraints Multiple Schemas and Naming Conventions
Improve Performance Understand SQL Mass data handling vs. loops Indexes (Clustered, Primary, non-Primary) Retrieve Computed Results Computed Columns Indexed Views Schemabinding of Views
Structuring Code for Re-Use Custom Data Types Limited support in SQL Server User Defined Functions (using Schemabinding) 3 Types (Static, inline TVF, multi-statement TVF) Inline TVF preferred for large data Static Functions make code easy to read and maintain Evaluate using Profiler! (ignored in query plans)
Security Concerns DDL statements (modify data structure, generate SQL Code) should be allowed upon install only Better if DNN would be using multiple Schemas Metadata (core only) Presentation Layer & Content Access Data only using Stored Procedures More effort, but: No risk of SQL injection Easy to optimize Be careful with Dynamic Queries SQL injection
Code Management Separate SQL Code (Views, Functions and Sprocs) from Updates of Data Structure (Tables.Columns) and Content Mark data updates being executed (upgrade scripts need to be re-runnable) Consider removing code and re-apply completely on each update: 1. Drop all your Views, Functions, Procedures 2. Migrate Data 3. Re-Create all Views, Functions, Procedures This allows you to manage versions of most of the code
