Relational Databases: Why So Many Tables?

1
Relational Databases Why So Many Tables?

• William Blozan, Ph.D.
• wblozan_at_fastanswers.com

Fast Answers
2
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.

3
Agenda

• Symptoms of Dysfunction
• The Challenge
• Whats 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

4
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

5
Vocabulary

• Flat File Spreadsheets and Word Processors.0 (
  misused RDBMSs)
• 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

6
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

7
Whats At Stake

• Stable Production System
• End User Direct Access
• Disaster Recovery
• Total Development Time Effort
• Data Conversion Complexity

8
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 gt All Sensible Reports are
  Feasible
• Must be able to describe an Arbitrary Record
  for each table

9
Recommended Steps For Modeling Tables

• Identify Primary Strategic Objects
• Each Object Becomes a Table
• Identify Multi-Valued Fields gt Each repeating
  field can mean a Child Table
• Identify M-M Relationships gt Each requires a
  Third Association Table

10
Recommended Steps For Modeling Tables, Cont

• 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 gt Temporary Tables may be
  necessary to isolate processes

11
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 gt Use Triggers
• Watch Out Time Order of Occurrence

12
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

13
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

14
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?
• gt One Screen Functionality Is Complex
• Unbound Form, Drop-Downs, Tricky Navigation
• Easy To Use gt Lots Of Code

15
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 LUs
• Prepare Save Queries/Scripts
• Trap Source Tables Keys

16
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!

17
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.