Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Creating your MySQL Database: Practical Design Tips and Techniques
  • Table Of Contents Toc
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques

By : Delisle
3.5 (2)
Buy this Book
close
close
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques

3.5 (2)
By: Delisle
Buy this Book

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)
close
close
close
Creating your MySQL Database: Practical Design Tips and Techniques
Icon
Creating your MySQL Database: Practical Design Tips and Techniques
Credits
Icon
Credits
About the Author
Icon
About the Author
About the Reviewer
Icon
About the Reviewer
Preface
Icon
Preface
Lock Free Chapter
1
Introducing MySQL Design
Icon
Introducing MySQL Design
Icon
MySQL's Popularity and Impact
Icon
Data as a Resource
Icon
Data Modeling
Icon
Case Study
Icon
The Tale of the Too Wide Table
Icon
Summary
2
Data Collecting
Icon
Data Collecting
Icon
System Boundaries Identification
Icon
Document Gathering
Icon
Interviews
Icon
Data Collected for our Case Study
Icon
Summary
3
Data Naming
Icon
Data Naming
Icon
Data Cleaning
Icon
Subdividing Data Elements
Icon
Data that are Results
Icon
Data as a Column's or Table's Name
Icon
Planning for Changes
Icon
Naming Recommendations
Icon
Summary
4
Data Grouping
chevron up
Icon
Data Grouping
Icon
Initial List of Tables
Icon
Rules for Table Layout
Icon
Improving the Structure
Icon
Validating the Structure
Icon
Summary
5
Data Structure Tuning
Icon
Data Structure Tuning
Icon
Data Access Policies
Icon
Storage Engines
Icon
Performance
Icon
In-Column Data Encoding
Icon
Case Study's Final Structure
Icon
Summary
6
Supplemental Case Study
Icon
Supplemental Case Study
Icon
Results from the Document Gathering Phase
Icon
Preliminary List of Data Elements
Icon
Tables and Sample Values
Icon
Airline System Data Schema
Icon
Sample Queries
Icon
Summary
1
Index
Icon
Index

Improving the Structure

Even when our table layout respects the rules, we can still refine it by looking at the following additional issues.

Scalability over Time

In Chapter 3 (section Data that are Results), we saw that we could avoid reserving a column for the tax amount, provided we have the exact tax rate in a reference table. However this rate could change so we need a more complete table that contains date ranges and the corresponding rate. This way, projecting the system over the time dimension, we can ensure that it will accommodate rate fluctuations. Note that the following sale table is not complete:

table: sale

column name

sample value

 

*date

2006-03-17

 

*internal_id

123

 

condition_code

N

table: condition

column name

sample value

 

*code

N

 

description

New

Comparing the date column from the sale table with the start_date and end_date from the following tax_rate table, we can find the exact tax rate for the date of sale:

table: tax_rate

column name

sample value

 ...
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Creating your MySQL Database: Practical Design Tips and Techniques
End of Section 4
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon