Hernandez - Database design for mere mortals: a hands-on guide to relational database design

A Hands-on Guide to Relational Database Design

Third Edition

Michael J. Hernandez

Upper Saddle River, NJ Boston Indianapolis San Francisco
New York Toronto Montreal London Munich Paris Madrid
Capetown Sydney Tokyo Singapore Mexico City

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals.

The author and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein.

The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales, which may include electronic versions and/or custom covers and content particular to your business, training goals, marketing focus, and branding interests. For more information, please contact:

U.S. Corporate and Government Sales
(800) 382-3419

For sales outside the United States, please contact:

International Sales

Visit us on the Web: informit.com/aw

Cataloging-in-Publication Data is on file with the Library of Congress.

Copyright 2013 by Michael J. Hernandez

All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. To obtain permission to use material from this work, please submit a written request to Pearson Education, Inc., Permissions Department, One Lake Street, Upper Saddle River, New Jersey 07458, or you may fax your request to (201) 236-3290.

ISBN-13: 978-0-321-88449-7
ISBN-10: 0-321-88449-3

Text printed in the United States on recycled paper at Edwards Brothers Malloy in Ann Arbor, Michigan.
First printing, February 2013

For my wife, who has always believed in me and continues to do so.

To those who have helped me along my journeyteachers, mentors, friends, and colleagues.

Dedicated to anyone who has unsuccessfully attempted to design a relational database.

Michael J. Hernandez has been an independent relational database consultant specializing in relational database design. He has more than twenty years of experience in the technology industry, developing database applications for a broad range of clients. Hes been a contributing author to a wide variety of magazine columns, white papers, books, and periodicals, and is coauthor of the best-selling SQL Queries for Mere Mortals (Addison-Wesley, 2007). Mike has been a top-rated and noted technical trainer for the government, the military, the private sector, and companies throughout the United States. He has spoken at numerous national and international conferences, and has consistently been a top-rated speaker and presenter.

Aside from his technical background, Mike has a diverse set of skills and interests that he also pursues, ranging from the artistic to the metaphysical. His greatest interest is still the guitar, as hes been a practicing guitarist for more than forty years and played professionally for fifteen years. He is a great cook, loves to teach (writing, public speaking, music), has a gift for bad puns, and even reads tarot cards.

He says hes never going to retire, per se, but rather just change whatever it is hes doing whenever he finally gets tired of it and move on to something else that interests him.

Here it is, ten years later, and Mike and I cross paths even less than we used to. For those who were unaware, we share the same birthday (although hes much older than me, at least one full year), and we meet up at least once each year and congratulate ourselves for making it another year. Its also funny how Microsoft reboots its technology every ten years or so, and now, revisiting the foreword I wrote ten years ago, nothing much has changedIm still hip-deep in a new Microsoft technology, but this time its all about WinRT and Windows 8, rather than .NET. One thing that hasnt changed, however, is the need for carefully planned and executed database design. Nothing Mike wrote in his original volume has changed very much, and although this new edition modifies some details, the basics of good database design havent changed in the ensuing ten years. I must confess a little jealousy that Mike has written a book with such enduring shelf life, but, if hes going to have a book that succeeds for this many years, at least its a good one. Whether this is your first visit to Mikes detailed explanation of database design, or your second or third, be assured that youll find a carefully considered, helpful path through the vagaries of database design here. But lets get past the intro, and get to work!

Ken Getz, November 14, 2012

I dont see Mike Hernandez as much as I used to. Both our professional lives have changed a great deal since I first wrote the foreword to his original edition. If nothing else, we travel less, and our paths cross less often than they did. If youll indulge me, I might try to add that the entire world has changed since that first edition. On the most mundane level, my whole development life has changed, since Ive bought into this Microsoft .NET thing whole-heartedly and full-time. One thing that hasnt changed, however, is the constant need for data, and well-designed data. Slapping together sophisticated applications with poorly designed data will hurt you just as much now as when Mike wrote his first editionperhaps even more. Whether youre just getting started developing with data, or are a seasoned pro; whether youve read Mikes previous book, or this is your first time; whether youre happier letting someone else design your data, or you love doing it yourselfthis is the book for you. Mikes ability to explain these concepts in a way thats not only clear, but fun, continues to amaze me.

Ken Getz, October 10, 2002

Perhaps youre wondering why the world needs another book on database design. When Mike Hernandez first discussed this book with me, I wondered. But the fact isas you may have discovered from leafing through pages before landing here in the forewordthe world does need a book like this one. You can certainly find many books detailing the theories and concepts behind the science of database design, but you wont find many (if any) written from Mikes particular perspective. He has made it his goal to provide a book that is clearly based on the sturdy principles of mathematical study, but has geared it toward practical use instead of theoretical possibilities. No matter what specific database package youre using, the concepts in this book will make sense and will apply to your database-design projects.

I knew this was the book for me when I turned to the beginning of and saw this suggestion: