16,79 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 108
Veröffentlichungsjahr: 2006
Ähnliche
Table of Contents
Creating your MySQL Database: Practical Design Tips and Techniques
Marc Delisle
Creating your MySQL Database: Practical Design Tips and Techniques
Copyright © 2006 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: November 2006
Production Reference: 1141106
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 10: 1-904811-30-2
ISBN 13: 978-1-904811-30-5
www.packtpub.com
Cover Image by www.visionwt.com
Credits
Author
Marc Delisle
Reviewer
Rudy Limeback
Development Editor
Louay Fatoohi
Assistant Development Editor
Nikhil Bangera
Technical Editor
Mithil Kulkarni
Editorial Manager
Dipali Chittar
Project Manager
Patricia Weir
Indexer
Bhushan Pangaonkar
Proofreader
Martin Brooks
Layouts and Illustrations
Shantanu Zagade
Cover Designer
Shantanu Zagade
About the Author
Marc Delisle is a member of the MySQL Developers Guild, which regroups community developers—because of his involvement with phpMyAdmin. He started to contribute to this popular MySQL web interface in December 1998, when he made the first multi-language version. He has been actively involved with the phpMyAdmin project since May 2001 as a developer and project administrator.
He has worked since 1980 at Collège de Sherbrooke, Québec, Canada, as an application programmer and network manager. He has also been teaching networking, security, Linux servers, and PHP/MySQL application development.
I would like to thank the whole Packt team for their support, especially Louay Fatoohi and Nikhil Bangera; their advice helped shaping this book. My thanks also go to Rudy Limeback for his insight.
The developers of the MySQL software have earned my respect; may they find here my warm gratitude for their excellent product.
I hope that this book will assist readers into building effective data structures.
To Carole, André, Corinne, Annie, and Guillaume, with all my love.
About the Reviewer
Rudy Limeback is an SQL Consultant with close to 20 years of experience using SQL in one database system or another. He is located in Toronto, Canada but, thanks to the miracle that is the Internet, consults for clients all over the wide world.More information on SQL and Web development can be found on Rudy's website, http://www.r937.com/.
Chapter 0. Preface
MySQL, launched in 1995, has become the most popular open source database system. The popularity of MySQL and phpMyAdmin has allowed many non-IT specialists to build dynamic websites with a MySQL backend. This book is a short but complete guide showing beginners how to design good data structures for MySQL. It teaches how to plan the data structure and how to implement it physically using MySQL's model.
What This Book Covers
Chapter 1 introduces the concept of MySQL, and discusses MySQL's growing popularity and its impact as a powerful tool. This chapter gives us a brief overview of the relational models and Codd's rules, which are required for designing purposes. A brief introduction to our case study—"car dealer" is provided at the end.
Chapter 2 shows how to deal with the raw data information that comes from the users or other sources, and the techniques that can help us build a comprehensive data collection. Also, this chapter covers the exact limits of the analyzed system, how one should gather documents, and interview activities for our case study.
Chapter 3 emphasises on transforming the data elements gathered in the collection process into a cohesive set of column names. The concept of data naming is also discussed in this chapter.
Chapter 4 provides the technique of grouping column names into tables. Rules for table layout, the concepts such as primary key, unique key, data redundancy, and data dependency are covered in this chapter.
Chapter 5 presents various techniques for improving our data structure in terms of security, performance, and documentation. The final data structure for the car dealer's case study is provided at the end.
Chapter 6 covers a supplemental case study about an airline system. This case study involves various steps such as gathering documents, preparing preliminary list of data elements, preparing a list of tables, sample values, and queries for the airline system.
What You Need for This Book
Basic knowledge of SQL is required. Emphasis is made on the phpMyAdmin web-based interface for reproducing the examples, although the "mysql" command-line tool can be used. No knowledge of MySQL server administration or any specific operating system is required.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
There are three styles for code. Code words in text are shown as follows: "In this case, we can add employee information, the employee code to the car_event table".
A block of code will be set as follows:
When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be made bold:
New terms and important words are introduced in a bold-type font. Words that you see on the screen, in menus, or dialog boxes for example, appear in our text like this: "It becomes impossible to link this "column" (for example the special paint color) to a lookup table".
Note
Warnings or important notes appear in a box like this.
Note
Tips and tricks appear like this.
Reader Feedback
Feedback from our readers is always welcome. Let us know what you think about this book, what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply drop an email to <[email protected]>, making sure to mention the book title in the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer Support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the Example Code for the Book
Visit http://www.packtpub.com/support, and select this book from the list of titles to download any example code or extra resources for this book. The files available for download will then be displayed.
The downloadable files contain instructions on how to use them.
Errata
Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing this you can save other readers from frustration, and help to improve subsequent versions of this book. If you find any errata, report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the Submit Errata link, and entering the details of your errata. Once your errata have been verified, your submission will be accepted and the errata added to the list of existing errata. The existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Questions
You can contact us at <[email protected]> if you are having a problem with some aspect of the book, and we will do our best to address it.
Chapter 1. Introducing MySQL Design
Data design is an essential part of the application development cycle. By analogy, building an application is like building a house. Having the right tools is important, but we need a solid foundation: the data structure. However, producing a good data structure can be a daunting challenge; the quest for a perfect data structure can lead us to new territories where many methods are available. Which one is the best? How can we keep our focus on the goal to achieve, without losing our time?
Data design for MySQL databases is both a science and an art, and there must be a good balance between the scientific and the empiric aspects of the method. The scientific aspect refers to information technology (IT) principles, whereas the empiric facet is mostly based on intuitions and experience.
This book is primarily oriented towards MySQL databases. It teaches how to plan the data structure and how to implement it physically using MySQL's model. The planning part is sometimes referred to as logical design, but it is preferable to view the logical/physical process as a whole.
MySQL's Popularity and Impact
MySQL (www.mysql.com), launched in 1995, has become the most popular open source database system. Virtually all web providers include MySQL as part of their hosting plan, often on the ubiquitous LAMP (Linux, Apache, MySQL, PHP) platform. Another root cause of MySQL's popularity has been the ongoing success of phpMyAdmin (www.phpmyadmin.net), a well-established MySQL web-based interface. Therefore many websites use MySQL as their back-end data repository.
The Need for MySQL Design
Overall, MySQL's popularity has attracted many web developers, some of them having no prior IT experience. When faced with the task of transforming a static website into a dynamic/transactional one, or integrating corporate data into the site, developers are sometimes inclined to improvise a data structure. This structure (or lack of structure) may work for a certain time but later fails because of lack of depth. Maybe the system initially works because it started small, with only a few functions planned and implemented, but falls apart when users ask more of it. A poorly designed data structure can only be patched to a certain extent. It can also have scaling issues, when the initial testing has been done with only a few rows of data.
