Transcript Recommended Steps For Modeling Tables

Relational Databases: Why So
Many Tables?
William Blozan, Ph.D.
[email protected]
Fast Answers
Introduction
• Database Development: Programming,
Data Theory & Business Management
• What is Relational Modeling?
• How Many Tables Do I Need, and Why so
Many?
• This Topic Is of Interest to All Levels of
Database Developers.
Agenda
•
•
•
•
•
•
•
•
•
•
Symptoms of Dysfunction
The Challenge
What’s at Stake
Key Design Concepts
Recommended Steps For Modeling Tables
Additional Modeling Complexity
Recommended Steps For Managing Tables
Managing Table Complexity in the GUI
Example
Plan Initial Data Conversion
Symptoms of Dysfunction
•
•
•
•
•
•
•
Insufficient Number of Fields.
Multiple Entry of Data.
Awkward Or Impossible Data Access.
Frequent User Entry Errors & Variations.
High Maintenance Complexity
Very Slow System Performance
Unwieldy Change Management
Vocabulary
• Flat File: Spreadsheets and Word
Processors.0 (& misused RDBMS’s)
• Boyce-Codd Normalization: The Theory is
only 25 yrs old!
• Object Modeling: Conceptual Layer
• Relational Modeling: Logical Layer
• DBMS: Handles the Physical Layer
• Entity-Relationship Diagrams
The Challenge
•
•
•
•
•
•
Seek To Design A Safe Data Repository
Seek To Provide Easy Data Entry
Seek To Provide Flexible Data Retrieval
Must Allow for Repository Extensions
Must Anticipate Additional Uses & Users
Seek Tolerable System Performance
What’s At Stake
•
•
•
•
•
Stable Production System
End User Direct Access
Disaster Recovery
Total Development Time & Effort
Data Conversion Complexity
Key Design Concepts
•
•
•
•
•
•
Single Source Data Repository
Application Independence
Objects are Nouns; Processes are Verbs
Process Logs are Nouns
Let the Data Speak!
Claim: Correct Model => All Sensible Reports are
Feasible
• Must be able to describe an “Arbitrary Record” for
each table
Recommended Steps For
Modeling Tables
• Identify Primary Strategic Objects
• Each Object Becomes a Table
• Identify Multi-Valued Fields => Each
repeating field can mean a Child Table
• Identify M-M Relationships => Each
requires a Third Association Table
Recommended Steps For
Modeling Tables, Con’t
•
•
•
•
Identify each Data Processing Step
Each process warrants at least one log table
Consider Lookup Tables and Value Lists
Consider Adding a System Table and an
Audit Log
• Consider other Application Meta Data
• Multi-User Processes => Temporary Tables
may be necessary to isolate processes
Additional Modeling Complexity
•
•
•
•
•
•
•
•
Use Content-Void Primary Keys
1-1 Table Relationships Still Require a Parent
Self Joins track genealogy
Partial RI For Optional Foreign Keys
Track Embedded Value Lists
Using Cascade Delete
Data Events => Use Triggers
Watch Out: Time Order of Occurrence
Recommended Steps For
Building Tables
•
•
•
•
•
•
Finalize the Entity-Relationship Data Model
Name Carefully: tblNounAdjVerbAdverb
Build the Tables and Relationships
Use AutoNumbers as Primary Keys
Add all data columns/fields
Define Data Types, Column Sizes, Default Values,
Indexing, Validation, etc.
• Add Audit Fields, Temporary Conversion Source
Fields
Manage Table Complexity In
Screens And Code
•
•
•
•
•
•
Start with Bound MS Access Forms
Linked Tabs and Sub-Forms
Zoom To Edit M-M Parents
Bound, But Empty
Events: BU, AU, AI, On Current, Triggers
Unbound Views Of Many Tables
Example: Business Cards
•
•
•
•
•
•
•
•
1-7 Tables
2 Key Objects: Firms & People
M-M Association: Employee w/ Job Title
Lookups: Phone Type, Address Type
Phones?, Addresses?
=> One Screen Functionality Is Complex
Unbound Form, Drop-Downs, Tricky Navigation
Easy To Use => Lots Of Code
Plan Initial Data Conversion
•
•
•
•
•
•
•
Map Old To New, New To Old
Spot Compacted Tables: Extract
Spot Compacted Fields: Extract
Check For Required Fields
Check For Consistent Spelling of LU’s
Prepare & Save Queries/Scripts
Trap Source Tables & Keys
Conclusion
•
•
•
•
Get It Right From The Start
OK, Then Fix It As Soon As You See It
Name Things Appropriately
Data Model Diagrams Aid Communication
With Clients and Colleagues
• Suppress Complexity From Users
• Art or Science? Both!
Where to get more information
•
•
•
•
•
•
•
Nothwind Traders – Sort of!
MS Access & SQL Server User Groups
Establish quid pro quo relationships
MS Access has “Analyzer” wizards
ERwin, Infomodeler, Visual Modeler
Database Processing, Kroenke & Dolan, SRA.
Understanding Relational Databases, Fabian
Pascal, Wiley.
• Consultants.