Creating your MySQL Database: Practical Design Tips and Techniques电子书

Creating your MySQL Database: Practical Design Tips and Techniques

Table of Contents

Creating your MySQL Database: Practical Design Tips and Techniques

Credits

About the Author

About the Reviewer

0. Preface

What This Book Covers

What You Need for This Book

Conventions

Reader Feedback

Customer Support

Downloading the Example Code for the Book

Errata

Questions

1. Introducing MySQL Design

MySQL's Popularity and Impact

The Need for MySQL Design

"What do I do Next?"

Data Design Steps

Data as a Resource

But this is my Data!

Data Modeling

Overview of the Relational Model

Rule #1

Rule #2

Simplified Design Technique

Case Study

Our Car Dealer

The System's Goals

The Tale of the Too Wide Table

Summary

2. Data Collecting

System Boundaries Identification

Modular Development

Model Flexibility

Document Gathering

General Reading

Forms

Existing Computerized Systems

Interviews

Finding the Right Users

Perceptions

Asking the Right Questions

Existing Information Systems

Chronological Events

Sources and Destinations

Urgency

Avoid Focusing on Reports and Screens

Data Collected for our Case Study

From the General Manager

From the Salesperson

From the Store Assistant

Other Notes

Summary

3. Data Naming

Data Cleaning

Subdividing Data Elements

Data Elements Containing Formatting Characters

Data that are Results

Data as a Column's or Table's Name

Planning for Changes

Pitfalls of the Free Fields Technique

Naming Recommendations

Designer's Creativity

Abbreviations

Clarity versus Length: an Art

Suffixing

The Plural Form

Naming Consistency

MySQL's Possibilities versus Portability

Table Name into a Column Name

Summary

4. Data Grouping

Initial List of Tables

Rules for Table Layout

Primary Keys and Table Names

Data Redundancy and Dependency

Composite Keys

Improving the Structure

Scalability over Time

Empty Columns

Avoiding ENUM and SET

Multilingual Planning

Validating the Structure

Summary

5. Data Structure Tuning

Data Access Policies

Responsibility

Security and Privileges

Views

Storage Engines

Foreign Key Constraints

Performance

Indexes

Helping the Query Optimizer: Analyze Table

Accessing Replication Slave Servers

Speed and Data Types

Table Size Reduction

In-Column Data Encoding

Case Study's Final Structure

Vehicle

Person

Sale

Other tables

Summary

6. Supplemental Case Study

Results from the Document Gathering Phase

Preliminary List of Data Elements

Tables and Sample Values

Code Tables

Themed Tables

Composite-Key Tables

Airline System Data Schema

Sample Queries

Inserting Sample Values

Boarding Pass

Passenger List

All Persons on a Flight

Summary

Index