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.
Table of Contents (12 chapters)

Index

A

  • airline system, case study
    • document gathering, results / Results from the Document Gathering Phase
    • data elements / Preliminary List of Data Elements
    • tables / Tables and Sample Values, Themed Tables
    • code tables / Code Tables
    • themed tables / Themed Tables
    • composite-key tables / Composite-Key Tables
    • sample queries / Sample Queries
    • boarding pass, sample query / Boarding Pass
    • passenger list, sample query / Passenger List
    • all persons on a flight, sample query / All Persons on a Flight

C

  • case study
    • final structure / Case Study's Final Structure
  • case study, car dealership
    • car dealer / Our Car Dealer
    • system goals / The System's Goals
    • scope / System Boundaries Identification
    • data elements, general manager / From the General Manager
    • data elements, salesperson / From the Salesperson
    • data elements, sales contract / From the Salesperson
    • data elements, store assistant / From the Store Assistant
    • example of data cleaning / Data Cleaning
  • composite key
    • using / Composite Keys
  • compound key
    • about / Composite Keys

D

  • data
    • data design, with case study / Case Study
    • data cleaning / Data Cleaning
    • as results / Data that are Results
    • data structure, changing / Planning for Changes
  • data, collecting
    • interviews / Interviews, Existing Information Systems
  • data access policies
    • about / Data Access Policies
    • data responsibility / Responsibility
    • security / Security and Privileges
    • previleges / Security and Privileges
  • data dependency
    • about / Data Redundancy and Dependency
  • data design
    • technique / Simplified Design Technique
  • data elements
    • examples / Data Collected for our Case Study
    • subdividing / Subdividing Data Elements
    • subdividing, example / Subdividing Data Elements
    • with formatting characters / Data Elements Containing Formatting Characters
  • data models
    • challenges / System Boundaries Identification
  • data naming
    • problems / Data as a Column's or Table's Name
    • about / Naming Recommendations
    • designer’s creativity / Designer's Creativity
    • abbreviations / Abbreviations, Suffixing
    • plural form / The Plural Form
    • consistency / Naming Consistency
    • MySQL issues / MySQL's Possibilities versus Portability
    • table name into a column name / Table Name into a Column Name
  • data structure
    • efficiency, improoving / Performance
  • document, gathering
    • goal / Document Gathering
    • general reading / General Reading
    • forms / Forms
    • existing system / Existing Computerized Systems

E

  • entity relationship diagram
    • about / Simplified Design Technique

F

  • foreign key
    • benefits / Foreign Key Constraints
  • free fields technique
    • about / Planning for Changes
    • example / Planning for Changes
    • drawbacks / Pitfalls of the Free Fields Technique

I

  • index
    • about / Indexes
    • creating / Indexes
    • creating on composite key / Indexes
    • EXPLAIN keyword / Indexes
  • interviews
    • goal / Interviews
    • users, finding / Finding the Right Users
    • users, finding issues / Finding the Right Users
    • perceptions / Perceptions
    • perceptions, example / Perceptions
    • relevant questions / Asking the Right Questions
    • relevant questions, existing system / Existing Information Systems
    • relevant questions, chronological events / Chronological Events
    • relevant questions, sources / Sources and Destinations
    • relevant questions, urgency / Urgency

M

  • modular development
    • priorities / Modular Development
  • MySQL
    • query optimizer / Helping the Query Optimizer: Analyze Table
    • VARCHAR datatype / Speed and Data Types
    • datatypes and storage methods / Speed and Data Types
    • TEXT datatype / Speed and Data Types

P

  • phpMyAdmin
    • PDF Page feature / Case Study's Final Structure
    • Display PDF schema / Case Study's Final Structure
    • Export feature / Case Study's Final Structure
  • primary key
    • about / Rule #2

R

  • relational model
    • overview / Overview of the Relational Model

S

  • storage engines, MySQL
    • pluggable storage engine architecture / Storage Engines
    • MyISAM / Storage Engines
    • InnoDB / Storage Engines
    • MEMORY / Storage Engines
    • NDB / Storage Engines
    • general guideline / Storage Engines
  • system boundaries
    • identifying / System Boundaries Identification
    • modular development / Modular Development
    • model flexibility / Model Flexibility

T

  • table
    • about / Rule #1
  • tables
    • list of tables / Initial List of Tables
    • rules for table layout / Rules for Table Layout
    • name, selection / Primary Keys and Table Names
    • reference tables / Data Redundancy and Dependency
    • code tables, laying out / Composite Keys
    • foreign key / Foreign Key Constraints
    • referencing tables / Foreign Key Constraints
    • InnoDB storage engine / Foreign Key Constraints
    • ON UPDATE CASCADE clause, used / Foreign Key Constraints
    • fixed table format / Speed and Data Types
    • size reduction / Table Size Reduction
    • data encoding / In-Column Data Encoding
  • table structure
    • improoving / Improving the Structure
    • scalability over time / Scalability over Time
    • empty columns / Empty Columns
    • SET / Avoiding ENUM and SET
    • ENUM, advantages / Avoiding ENUM and SET
    • ENUM, disadvantages / Avoiding ENUM and SET
    • ENUM and SET, avoiding / Avoiding ENUM and SET
    • multilingual application / Multilingual Planning
    • validating / Validating the Structure
  • table structure
    • comments, adding to columns in table / Responsibility
    • Row Statistics section / Speed and Data Types
  • too wide table
    • about / The Tale of the Too Wide Table
    • need for / The Tale of the Too Wide Table
    • example / The Tale of the Too Wide Table
    • script, for creating example / The Tale of the Too Wide Table
    • example, phpMyAdmin, used / The Tale of the Too Wide Table
    • example, problem, examining / The Tale of the Too Wide Table

U

  • unique key
    • about / Primary Keys and Table Names

V

  • views
    • about / Views
    • uses / Views
    • previleges / Views