Data Driven DesignsDatabase DiagramsForeign Key RelationSlide 4Foreign Key ConstraintsData ConstraintsIndexesStored ProceduresTriggersGeneral Data Design RulesSpecific Data Design RulesReadData Driven DesignsData Driven Designs99% of enterprise applications operate on database data or at least interface databases.Most common DBMS are Microsoft SQL Server, Oracle, MySQL.Most development environments (including Visual Studio) support database integration and / or interface with data modeling tools.Tight and accurate data model is essential for a good data-driven design.Data modeler / data analyst and DBA has to be on the development team.Database server is required for the application development and deployment.Application load may become an issue due to database server licensing limitations.Database DiagramsDatabase DiagramsForeign Key RelationForeign Key Relation +-----+ One-to-one relation +o----+ Zero/one-to-one relation +o---+o< One-to-zero/many relation +o----+o< Zero/one-to-zero/many relationAllFusion® Erwin® is a popular data modeling toolForeign Key RelationForeign Key RelationSQL Server Foreign Key Relation DiagramForeign Key ConstraintsForeign Key ConstraintsLinked to foreign primary key / key values.Primary key values should never change.Must be enforced to preserve data integrity.Prevent orphaned child records.Prevent childless parent records.Can be enforced via cascade delete / cascade update mechanism.Data ConstraintsData ConstraintsUseful for limiting the range of column values. Must use!Example: Grade IN ('A','B','C','D','F')IndexesIndexesGreatly improve query performance especially when joining tables and sorting.Stored ProceduresStored ProceduresData manipulation code stored in the database.Stored procedures can result in select queries, update or insert operations or any other non-trivial data manipulation / calculation.Many believe that it is not a good idea to encode business rules into a database hence stored procedures should never be used or at least should be minimized.TriggersTriggersTriggers are stored procedures that are executed based on specific events.For instance you can define for a particular table a trigger, which would be fired when a row is in that table is inserted, deleted or updated.Triggers are notoriously hard to see and hard to remember. They cause numerous problems when used excessively.General Data Design RulesGeneral Data Design Rules1. Store as little data as possible.2. Create as tight as possible of a database structure preserving and controlling data integrity in the strictest possible way.-Enforce foreign key constraints-Enforce data constraints-Use NOT NULL attributesSpecific Data Design RulesSpecific Data Design Rules1. Avoid Duplication by Normalizing Your Data2. Consolidate Related Non-Shared Information by Denormalizing Your Data3. Always Define Data Relations and Foreign-Key Constraints4. Always Define Primary Keys for All Tables 5. Declare User-Defined Types for Range-Limited and Enumerated Values 6. Declare Required Columns as NOT NULL7. Assign Default Values to Columns When Possible 8. Define Indexes For Foreign Keys and Frequent Filter Criteria Columns9. Do Not Embed Business Rules in Database: Avoid Stored Procedures, Complex Check Constraints, Triggers10. Validate Data Both on Client Side and on Database SideReadReadChapter 11 from my