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)

Validating the Structure


Validation is done by using precise examples, asking ourselves if we have a column to place all information, covering all cases. Maybe there will be exceptions—what to do with those? Should our structure handle them? We can assess the risk factor associated with those exceptions, versus the cost of handling them and the possible loss in performance for the queries.

An example of an exception: a customer buys two cars the same day—this could influence the choice of primary key, if a date is part of this key, it will be conducive to add a column to this key: the time of day for the sale.

The phpMyAdmin utility can prove useful here. Tables are easily built with this software, while its index management feature permits us to craft our primary keys. Then we can use the multi-table query generator to simulate various reports and what-ifs.