Transcript Data Design Rules

Data Driven Designs
99% 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 Diagrams
Foreign Key Relation
AllFusion® Erwin® is a popular data modeling tool
+-----+ One-to-one relation
+o----+ Zero/one-to-one relation
+o---+o< One-to-zero/many relation
+o----+o< Zero/one-to-zero/many relation
Foreign Key Relation
SQL Server Foreign Key Relation Diagram
Foreign Key Constraints
Linked 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 Constraints
Useful for limiting the range of
column values. Must use!
Example: Grade IN ('A','B','C','D','F')
Indexes
Greatly improve query performance
especially when joining tables
and sorting.
Stored Procedures
Data 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.
Triggers
Triggers 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 Rules
1. 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 attributes
Specific Data Design Rules
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Avoid Duplication by Normalizing Your Data
Consolidate Related Non-Shared Information by
Denormalizing Your Data
Always Define Data Relations and Foreign-Key
Constraints
Always Define Primary Keys for All Tables
Declare User-Defined Types for Range-Limited and
Enumerated Values
Declare Required Columns as NOT NULL
Assign Default Values to Columns When Possible
Define Indexes For Foreign Keys and Frequent Filter
Criteria Columns
Do Not Embed Business Rules in Database: Avoid Stored
Procedures, Complex Check Constraints, Triggers
Validate Data Both on Client Side and on Database Side
Read
Chapter 11 from my book.