Book Image

Creating your MySQL Database: Practical Design Tips and Techniques

By : Marc Delisle
Book Image

Creating your MySQL Database: Practical Design Tips and Techniques

By: Marc Delisle

Overview of this book

For most of us, setting up the database for an application is often an afterthought. While you don't need to be a professional database designer to create a working application, knowing a few insider tips and techniques can make both the process easier and the end result much more effective. This book doesn't set out to make you an expert in data analysis, but it does provide a quick and easy way to raise your game in this essential part of getting your application right.

Related Content you might be interested in

Current Title:

Small book image
Creating your MySQL Database: Practical Design Tips and Techniques
Marc Delisle
Nov, 2006 | 0 hours 0 minutes
No titles found
Table of Contents (12 chapters)
Creating your MySQL Database: Practical Design Tips and Techniques
Creating your MySQL Database: Practical Design Tips and Techniques
Credits
Credits
About the Author
About the Author
About the Reviewer
About the Reviewer
Preface
Preface
Free Chapter
1
Introducing MySQL Design
Introducing MySQL Design
MySQL's Popularity and Impact
Data as a Resource
Data Modeling
Case Study
The Tale of the Too Wide Table
Summary
2
Data Collecting
Data Collecting
System Boundaries Identification
Document Gathering
Interviews
Data Collected for our Case Study
Summary
3
Data Naming
Data Naming
Data Cleaning
Subdividing Data Elements
Data that are Results
Data as a Column's or Table's Name
Planning for Changes
Naming Recommendations
Summary
4
Data Grouping
Data Grouping
Initial List of Tables
Rules for Table Layout
Improving the Structure
Validating the Structure
Summary
5
Data Structure Tuning
Data Structure Tuning
Data Access Policies
Storage Engines
Performance
In-Column Data Encoding
Case Study's Final Structure
Summary
6
Supplemental Case Study
Supplemental Case Study
Results from the Document Gathering Phase
Preliminary List of Data Elements
Tables and Sample Values
Airline System Data Schema
Sample Queries
Summary
Index
Index

Case Study's Final Structure

In this section we examine the final data structure for our case study. There are many ways to present this structure. First we'll see all the tables that are related to each other—almost all tables are—then we will examine group of related tables and their columns.

The following schema is produced by phpMyAdmin's PDF Page feature. To access this feature, we open a database and access the Operations sub-page. Then we click Edit PDF pages.

When generating the PDF schema, we can also ask phpMyAdmin to produce a data dictionary. In order to do this, we click the Data Dictionary checkbox in the Display PDF schema dialog. Here is the page of this dictionary describing the person table:

This combined data dictionary/schema offers a noteworthy feature: we can click on a table name in the schema to reach the table's description in the dictionary, and the other way around.

The following CREATE TABLE commands come directly from the Export feature of phpMyAdmin. To access...

Previous Section
End of Section 6
Next Section