Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)02

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:15

0
59
lượt xem
5
download

Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)02

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)02

Chủ đề:
Lưu

Nội dung Text: Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)02

  1. xiv sCONTENTS Character Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619 char[(length)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619 varchar[(length)]. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620 varchar(max) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621 text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622 Unicode Character Strings: nchar, nvarchar, nvarchar(max), ntext . . . . . 622 Binary Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 binary[(length)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 varbinary[(length)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624 varbinary(max) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624 image . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625 Other Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625 rowversion (a.k.a. timestamp) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625 uniqueidentifier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626 cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 sql_variant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632 Not Simply Scalar Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 sINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
  2. Foreword D atabase design is a passion that Louis and I share. This fall, I will be fortunate to share a stage with my friend, Louis Davidson. We’re copresenting at DevLink in Louis’s hometown of Nashville and then again at PASS in Seattle. Both times, we’ll discuss, debate, and celebrate the topic of database design and how critical it is for any datacentric application. Database design is part science and part art. There’s a science to normalization, but determining the scope of the entity is an art form best learned from working with a broad repertoire of databases. There’s a simple beauty to an elegant solution. Louis is more than an author or database designer; Louis is a master artisan, and in this book, you’ll discover hidden within the words a call to improve your craft, to sculpt databases that stand the test of time, and to create virtual worlds of data that enable developers to play their best game. Database design is the foundation of every datacentric application. An elegant database design makes the data obvious and easy to query and sets up the developer for success with efficient set- based queries. But, no amount of code can compensate for a poor database design or add features missing from the database. No role is more critical to any datacentric application than the role of the data modeler. User interfaces come and go, but data lasts for generations of application lan- guages, and today’s database schema errors will be cursed by programmers not yet born using languages and tools not yet invented. It’s worth spending a little extra time to polish your database design under the tutelage of my good friend, Louis. So it’s with great pleasure that I welcome you to this third edition of Louis’s field guide to the greatest job in the world of software. Paul Nielsen SQL Server MVP xv
  3. About the Author sLOUIS DAVIDSON has over 15 years of experience as a corporate database developer and architect. Currently, he is the data architect for the Christ- ian Broadcasting Network and NorthStar Studios in Nashville, Tennessee. Nearly all of Louis’s professional experience has been with Microsoft SQL Server, from the early days to the latest version currently in beta. Louis has been the principal author on four editions of a book on database design. Louis’s primary areas of interest are database architecture and coding in T-SQL, and he has experience designing many databases and writing thousands of stored procedures and triggers through the years. About the Contributing Authors sKEVIN KLINE is the technical strategy manager for SQL Server solutions at Quest Software. A Microsoft SQL Server MVP since 2004, Kevin is a founding board member and past president of the international Professional Associa- tion for SQL Server (PASS). He has written or cowritten several books including SQL in a Nutshell (O’Reilly, 2004), Pro SQL Server 2005 Database Design and Optimization (Apress, 2006), and Database Benchmarking: Practical Methods for Oracle & SQL Server (Rampant, 2007). Kevin contributes to SQL Server Magazine and Database Trends and Applications and blogs at SQLBlog.com and SQLMag.com. Kevin is also a top-rated speaker at confer- ences worldwide, such as Microsoft Tech Ed, the PASS Community Summit, Microsoft IT Forum, DevTeach, and SQL Connections, and has been active in the IT industry since 1986. sSCOTT KLEIN is an independent consultant with a passion for all things SQL Server, .NET, and XML. He is the author of several books, including Professional SQL Server 2005 XML (Wrox, 2006) and Professional LINQ (Wrox, 2008), and he writes the biweekly feature article for the SQL PASS Community Connector. He has also contributed articles for several web sites, including Wrox (http://www.wrox.com) and TopXML (http://www.topxml.com). He fre- quently speaks to SQL Server and .NET user groups around Florida. Scott lives in Wellington, Florida, and when he is not sitting in front of a computer, he can be found hanging out with his family or aboard his Yamaha at the local motocross track. He can be reached at ScottKlein@SqlXml.com. sKURT WINDISCH is the applications supervisor for the internal IT department at Levi, Ray, and Shoup, Inc., a global provider of technology solutions with headquarters in Springfield, Illinois. He has more than 17 years of experience in the IT industry. He spent five years serving on the board of directors for the Professional Association for SQL Server, has written for several SQL Server magazines, and has presented at conferences internationally on the topic of database programming with SQL Server. xvii
  4. About the Technical Reviewers sWAYNE SNYDER is recognized worldwide as a SQL Server expert and Microsoft Most Valued Professional (MVP), and he has over 25 years of experience in project management, database administration, software design, performance measurement, and capacity planning. He is a sought-after speaker, consultant, writer, and trainer. Wayne is the current president of the Professional Association for SQL Server (PASS) (www.sqlpass.org) and a managing consultant for Mariner, a business intelligence company (www.mariner-usa.com). He also plays keyboard for a regional cover band named SoundBarrier (www.soundbarrierband.com). sEVAN TERRY is a lead consultant working at The Clegg Company, specializing in data modeling, data quality, and information management. His past and current clients include the State of Idaho; Albertsons; American Honda Motors; and Toyota Motor Sales, USA. He is the coauthor of Apress’s Beginning Relational Data Modeling (2005) and was a presenter at the 2006 and 2007 IAIDQ confer- ences and at the 2008 DAMA International Conference on the subject of data quality. He can be reached at evan_terry@cleggcompany.com. sDON WATTERS is a Staff Software Engineer at the Walt Disney Internet Group in Seattle, Washington. He has been working on Microsoft SQL Server products since version 6.5, and has over 10 years of experience with the product. He has extensive administrative and developer experience with very large OLTP and OLAP systems and enjoys learning and applying his knowledge to new and interest- ing database challenges. In his free time, Don enjoys spending time with his children, playing music and video games, and of course, studying all things related to Microsoft SQL Server. He can be reached at DonRWatters@aol.com. xix
  5. Acknowledgments If I have seen further, it is by standing on the shoulders of giants. —Sir Isaac Newton I am not a genius, nor am I some form of pioneer in the database design world. I acknowledge that the following “people” have been extremely helpful in making this book happen. Some helped me directly, while others probably don’t even know that this book exists. Either way, they have all been an important part of the process. Far above anyone else, Jesus Christ, without whom I wouldn’t have had the strength to com- plete the task of writing this book: I know I am not ever worthy of the love that You give me. My wife Valerie Davidson and daughter Amanda Davidson for putting up with this craziness for a fourth time (and helping me with the picture for the cover). My mom for just being there to keep me entertained with information about the world around her, and my mother-in-law, too, for helping us out at times. My best friend in the world who got me started with computers back in college when I still wanted to be a mathematician. I miss him far more than he will probably ever know; some day, I need to find him again and thank him where he might actually see it. My mentors Mike Farmer, Chip Broecker, and Don Plaster for the leadership they gave me in my early years. Gary Cornell for giving me a chance to write the book that I wanted to write. Frank Castora and Don Watters for doing beta reads of the book on their own time. (Don later became a full technical editor on the book!) Evan Terry and Wayne Snider for their technical edits, even if the process when a little off center and didn’t end up as we started. My current manager Rob Murdoch for giving me time to go to several conferences that really helped me to produce as good of a book as I did. All of my coworkers at CBN and the now- defunct Compass, who gave me many examples for the book. Scott Klein, Kevin Kline, and Kurt Windisch for taking up the slack with topics I didn’t want to (OK, couldn’t) tackle. Paul Nielsen for offering and taking the time to write a foreword to this book. The fantastic editing staff I’ve had, including Jonathan Gennick who (figuratively) busted my lip a few times over my poor use of the English language and without whom the writing would sometimes appear to come from an illiterate baboon. Most of these people are included on the copyright page, but I want to say a specific thanks to Tony Davis (who had a big hand in the book last time) for making this book great, despite my frequently rambling writing style. Raul Garcia, who works on the Microsoft SQL Server Engine team, for information about using EXECUTE AS and certificate-based security. Isaac Kunen for the discussions at Tech Ed that helped me understand spatial datatypes better. James Manning for the advice on READ COMMITTED SNAPSHOT. xxi
  6. xxii sACKNOWLEDGMENTS Chuck Heinzelman for being such a good friend and giving me the chance to write the article for SQL Server Standard around the time of this book coming out. All the MVPs that I’ve worked with over the past year and a half. Never a better group of folks have I found. Steven Dybing, Ben Miller, and now Ali Brooks have been great to work with. I want to list a few others individually for things they’ve specifically done to help me out: Dejan Sarka and Andrew Watt reviewed the previous version of the book with incredible vigor and didn’t let me slide on even small points. Hugo Kornelis gave me the most negative criticism of the previous version of the book; he really opened my eyes to some of the weaknesses (if only I could have gotten him to as one of the technical reviewers!). Steve Kass gave me the code for demonstrating what’s wrong with the money datatypes, as well as cool solutions to problems in newsgroups that made me think. Erland Somarskog helped me understand a bit more about how error handling works, and many other topics (not to mention providing his great website, http://www.sommarskog.se/). Adam Machanic helped me with many topics on my blog and in newsgroups. Aaron Bertrand deserves thanks for his great website http://www.aspfaq.com and the shoe memories. Thanks to Kalen Delaney for all she has done for me and the community and to Dr. Greg Low for putting me on his http://www.sqldownunder.com podcast. Kim Tripp provided a wonderful paper on SNAPSHOT isolation levels, and Arnie Rowland got me the two gigs with Microsoft Learning that helped me out in parts of the book. Thanks to Allen White for the times at Tech Ed and the horrible interview and to Jason Follas for the time at Tech Ed where I listened to you talking about the spatial types. I also want to thank Tony Bain, Hillary Cotter, Mike Epprecht, Geoff Hiten, Tom Moreau, Andrew Kelly, Tony Rogerson, Linchi Shea, Paul Nielson, Tibor Karaszi, Greg Linwood, Peter Debetta, Dr. Tom Moreau, Dan Guzman, Jacco Schalkwijk, Anith Sen, Jasper Smith, Ron Talmage, Christian Lefter, and Kent Tegels, because all of you have specifically helped me out over the past years in the newsgroups, teaching me new things to make my book far better. To the academics out there who have permeated my mind with database theory, such as E. F. Codd, Chris Date, Fabian Pascal, Joe Celko, my professors at the University of Tennessee at Chattanooga, and many others: I wouldn’t know half as much without you. And thanks to Mr. Date for reviewing Chapter 1; you probably did more for the next version of this book than the current one. And to Jim Gray and Ken Henderson, who both were amazing advocates of SQL Server and have inspired me over the years. Even with this large number of folks I have mentioned here, I am afraid I may have missed someone. If so, thank you! Louis Davidson
  7. Introduction I often ask myself, “Why do I do this? Why am I writing another edition of this book? Is it worth it? Couldn’t I help Mario save the princess faster if I just chucked the book and played Nintendo?” These questions were answered again for me by a fellow MVP at the Microsoft MVP Summit in 2008. He thanked me for writing this book and said that he’d tried to read the academic books on the subject and they were hard for him to follow. “Oh yeah,” I thought, “that was why I started out to do this thing in the first place.” When I was first getting started designing databases, I learned from a few great mentors, but as I wanted to progress, I started looking for material on database design, and there wasn’t much around. The best book I found was Chris Date’s An Introduction to Database Systems (Addison Wesley, 2003), and I read as much as I could comprehend. The problem, however, was that I quickly got lost and started getting frustrated that I couldn’t readily translate the theory of it all into a design process that really is quite simple once you get the ideas down. In Chris’s book, and in other textbooks I had used, it became clear that a lot of theory, and even more math, went into creating the relational model. If you want to be a theorist, Chris’s book is essential reading, along with lots of other books (here is a good place to start looking for more titles: http://www.dbdebunk.com/books.html). The problem is that most of these books have far more theory than the average practitioner wants (or will take the time to read), and they don’t really get into the actual implementation of a real database system. My book’s goal is simply to fill that void and bridge the gap between academic textbooks and the purely implementation-oriented books that are commonly written on SQL Server—my intention is not to knock those books, not at all; I have numerous versions of those types of books on my shelf. This book is more of a technique-oriented book than a how-to book teaching you the features of SQL Server. I will cover many the most typical features of the relational engine, giving you techniques to work with. I can’t, however, promise that this will be the only book you need on your shelf. If you have previous editions of this book, you might question why you need this edition, and I know the feeling. I spent a lot of time trying to figure out why you should buy this new edition, and my reason isn’t the obvious one—that now I cover 2008 features. Clearly, that is a part of it, but the biggest thing is that I continue to come up with new content to make your job easier. I’ve added another chapter about patterns of development (Chapter 7), and every chapter has a good amount of new material to help enhance your database designs. Oscar Wilde, the poet and playwright, once said, “I am not young enough to know everything.” It is with some chagrin that I must look back at the past and realize that I thought I knew everything just before I wrote my first book, Professional SQL Server 2000 Database Design. It was ignorant, unbridled, unbounded enthusiasm that gave me the guts to write the first book. In the end, I did write that first edition, and it was a decent enough book, largely due to the beating I took from my technical editing staff. And if I hadn’t possessed the enthusiasm initially that drove me to finish, I likely would not be writing this fourth edition of the book. However, if you had a few weeks to burn and you went back and compared each edition of this book, chapter by chapter, section by section, to the current edition, you would notice a progression of material and a definite maturing of the writer. There are a few reasons for this progression and maturity. One reason is the editorial staff I have had over the past two versions: first Tony Davis and now Jonathan Gennick. Both of them were very tough on my writing style and did wonders for the structure of the book. Another reason is simply xxiii
  8. xxiv sINTRODUCTION experience, as over eight years have passed since I started the first edition. But most of the reason that the material has progressed is that it’s been put to the test. While I have had my share of nice comments, I have gotten plenty of feedback on how to improve things (some of those were not-nice comments!). And I listened very intently, keeping a set of notes that start on the release date. I am always happy to get any feedback that I can use (particularly if it doesn’t involve any anatomical terms for where the book might fit). I will continue to keep my e-mail address available (louis@drsql.org), and you can leave anonymous feedback on my website if you want (drsql.org). You will also find an addendum there that covers any material that I wish I had known at the time of this writing. Purpose of Database Design What is the purpose of database design? Why the heck should you care? The main reason is that a properly designed database is straightforward to work with, because everything is in its logical place, much like a well-organized cupboard. When you need paprika, it’s easier to go to the paprika slot in the spice rack than it is to have to look for it everywhere until you find it, but many systems are organized just this way. Even if every item has an assigned place, of what value is that item if it’s too hard to find? Imagine if a phone book wasn’t sorted at all. What if the dictionary was organized by placing a word where it would fit in the text? With proper organization, it will be almost instinc- tive where to go to get the data you need, even if you have to write a join or two. I mean, isn’t that fun, after all? You might also be surprised to find out that database design is quite a straightforward task and not as difficult as it may sound. Doing it right is going to take more time at the beginning of a proj- ect than just slapping together the data storage as you go along, but it pays off throughout the full life cycle of a project. This brings me to one of the most challenging things about doing database design right: it takes more time than not doing it (this is a battle that can occur frequently in project planning meetings). Because there’s nothing visual to excite the client, database design is one of the phases of a project that often gets squeezed to make things seem to go faster. Even the least chal- lenging or uninteresting user interface is still miles more interesting to the average customer than the most beautiful data model. Programming the user interface takes center stage, even though the data is generally why a system gets funded and finally created. It’s not that your colleagues won’t notice the difference between a cruddy data model and one that’s a thing of beauty. They certainly will, but the amount of time required to decide the right way to store data correctly can be over- looked when programmers need to code. I wish I had an answer for that problem, because I could sell a million books with just that. This book will assist you with some techniques and processes that will help you through the process of designing databases, in a way that’s clear enough for novices and helpful to even the most seasoned professional. This process of designing and architecting the storage of data belongs to a different role from those of database setup and administration. For example, in the role of data architect, I seldom create users, perform backups, or set up replication or clustering. Little is mentioned of these tasks, which are considered administration and the role of the DBA. It isn’t uncommon to wear both a developer hat and a DBA hat (in fact, when you work in a smaller organization, you may find that you wear so many hats your neck tends to hurt), but your designs will generally be far better thought out if you can divorce your mind from the more implementation-bound roles that make you wonder how hard it will be to use the data. For the most part, database design looks harder than it is.
  9. sINTRODUCTION xxv sNote To be safe, I have to make one thing clear: if you’ve done any programming, you’ll undoubtedly disagree with some of the opinions and ideas in this book. I fully accept that this book is hardly the gospel of St. Louis of Katmai. My ideas and opinions have grown from more than 16 years of working with and learning about data- bases, supplemented with knowledge from many disparate people, books, college classes, and seminars. I thank many of these sources in the Acknowledgements, but there have been hundreds more whose names I’ve forgot- ten, although I’ve had some tidbit of knowledge imprinted on my brain from them. The design methodology presented in this book is a conglomeration of these ideas. I hope it proves a useful learning tool, and that through reading this book and other people’s works, plus a healthy dose of trying out your own ideas, you’ll develop a methodology that will suit you and will make you a successful database designer. Structure of This Book This book is composed of the following chapters: Chapter 1: Introduction to Database Concepts: This chapter provides a basic overview of essen- tial terms and concepts. Chapter 2: The Language of Data Modeling: This chapter serves as the introduction to the main tool of the data architect—the model. In this chapter, I introduce one modeling language (IDEF1X) in detail, as it’s the modeling language that’s used throughout this book to present database designs. I also introduce a few other common modeling languages, for those of you who need to use these types of models for preference or corporate requirements. Chapter 3: Conceptual Data Modeling: In conceptual modeling, the goal is to discuss the process of taking a customer’s set of requirements and to put the tables, columns, relation- ships, and business rules into a data model format where possible. Chapter 4: The Normalization Process: The next step in the database design process is normal- ization. The goal of normalization is to take the set of tables, columns, relationships, and business rules and format them in such a way that every value is stored in one place and every table represents a single entity. Normalization can feel unnatural the first few times you do it, because instead of worrying about how you’ll use the data, you must think of the data and how the structure will affect that data’s quality. However, once you’ve mastered normalization, not storing data in a normalized manner will feel wrong. Chapter 5: Implementing the Base Table Structures: This is the first point in the database design process in which we fire up SQL Server and start building scripts to build database objects. In this chapter, I cover building tables—including choosing the datatype for columns—as well as relationships. Part of this discussion notes how the implemented structures might differ from the model that we arrived at in the normalization process. Chapter 6: Protecting the Integrity of Your Data: Beyond the way data is arranged in tables and columns, other business rules may need to be enforced. The front line of defense for enforcing data integrity conditions in SQL Server is formed by CHECK constraints and triggers, as users cannot innocently avoid having constraints and triggers do their validations. I also discuss the various other ways that data protection can be enforced using stored procedures and client code.
  10. xxvi sINTRODUCTION Chapter 7: Patterns and Query Techniques: Beyond the basic set of techniques for table design, there are several techniques that I use to apply a common data/query interface for my future convenience in queries and usage. This chapter will cover several of the common useful pat- terns as well as taking a look at some patterns that some people will use to make things easier to implement the interface that can be very bad for your query needs. Chapter 8: Securing Access to the Data: Security is high in most every programmer’s mind these days, or it should be. In this chapter, I cover some strategies to use to implement data security in your system, such as employing views, triggers, encryption, and even using SQL Server Profiler. Chapter 9: Table Structures and Indexing: In this chapter, I show the basics of how data is struc- tured in SQL Server, as well as some strategies for indexing data for better performance. Chapter 10: Coding for Concurrency: Part of the database design and implementation process is to step beyond the structures and consider how to maximize resource utilization among many users. In this chapter, I describe several strategies for how to implement concurrency in your data access and modification code. Chapter 11: Considering Data Access Strategies: In this chapter, many of the concepts and con- cerns of writing code that accesses SQL Server are covered. I cover ad hoc SQL versus stored procedures (including all the perils and challenges of both, such as plan parameterization, per- formance, effort, optional parameters, SQL injection, and so on), as well as discussing whether T-SQL or CLR objects are best, including samples of the different types of objects that can be coded using the CLR. The material on the CLR was provided by Kurt Windisch, as was the downloadable CLR sample code. Chapter 12: Database Interoperability: Finally, in this chapter written by Kevin Kline, the chal- lenges of building databases that have to run on not only SQL Server but also other database server platforms are discussed. This chapter can be found on the Apress website, where it’s avail- able as a bonus download. Appendix A: Codd’s 12 Rules for an RDBMS: In this appendix, I present Codd’s original 12 rules for how a database should be implemented. As hardware improves, we are getting closer to finally realizing his dreams. Appendix B: Scalar Datatype Reference: In this appendix, I present all of the types that can be legitimately considered scalar types, along with why to use them, their implementation infor- mation, and other details. Appendix C: Beyond Relational Datatype: In this appendix, the so-called beyond relational types are discussed. Included are XML, the spatial types geography and geometry, and the hierarchyId type. The XML and spatial types sections were written by Scott Klein. This chapter can be found on the Apress website, where it's available as a bonus download. Again, please don’t hesitate to give me feedback on the book anytime (well, as long as you don’t call me at three in the morning). I’ll try to improve any sections that people find lacking and publish them to my blog (http://sqlblog.com/blogs/louis_davidson) with the tag DesignBook as well as to my website (http://drsql.org/ProSQLServerDatabaseDesign.aspx). I’ll be putting more informa- tion in both places as it becomes available pertaining to new ideas, goof ups I find, or additional materials that I choose to publish because I think of them once this book is no longer a jumble of bits and bytes and is an actual instance of ink on paper.
  11. CHAPTER 1 sss Introduction to Database Concepts And then she understood the devilish cunning of the enemies’ plan. By mixing a little truth with it they had made their lie far stronger. —C. S. Lewis, The Last Battle F rom the beginning of my career as a data architect to just yesterday (no matter what day you read this, what I’m about to say is extremely unlikely to change), I have encountered one reality that was completely difficult to beat. Doing things right is never an easy sale to a management system where the clock on the wall is the primary driver for all projects. Couple that with the fact that there are usu- ally many times more functional programmers waiting on the database, all telling management that they can’t do anything until the database is designed and at least started to be implemented. If that isn’t enough, this is about the time that a little inaccuracy infects the mind-set of the project team: “Database design is not that important.” It is rarely said explicitly in those words (though it is sometimes!) but generally is mixed in with good talk about how the user interface (UI) needs to show this and this button needs to go there. The design of the system starts to feel like a photo-realistic artist painting a picture, rather than a project that needs to follow solid engineering practices, starting with answering the questions of what needs to be done and then how to do it. Even worse, since the database is the backbone of almost any software project, it compounds the problem, which will often rear its ugly near the end of the development process. If you are involved in designing the database, then it is important to understand the fundamental answer to the question, “why?” Look at it this way, would you drive on a bridge designed by an engineer who did not understand physics? Or would you get on a plane designed by someone who didn’t understand the fundamentals of flight? Sounds quite absurd, right? So, would you want to store your important data in a database designed by someone who didn’t understand the fundamentals of database design? The first four chapters of this book are devoted to the distinct phases of relational database design and how to carry out each phase effectively so you are able to arrive at a final design that can fulfill the business requirements and ensure the integrity of the data in your database. However, before starting this design process in earnest, you need to explore a few core relational database concepts. Therefore, this chapter discusses the following topic areas: 1
  12. 2 CHAPTER 1 s INTRODUCTION TO DATABASE CONCEPTS • Database design phases: The next section provides an overview of the four major phases of relational database design: conceptual, logical, implementation, and physical. For time and budget reasons, it is often tempting to skip the earlier database design phases and move straight to the implementation phase. This chapter will explain why skipping any or all of these phases can lead to an incomplete and/or incorrect design, as well as one that does not support high-performance querying and reporting. • Relational data structures: This chapter will provide concise descriptions of some of the fun- damental database objects, including the database itself, as well as tables, columns, and keys. These objects are likely familiar to most, but there are some common misunderstand- ings in their usage that can make the difference between a mediocre design and a high-class, professional design. In particular, misunderstanding the vital role of keys in the database can lead to severe data integrity issues and to the mistaken belief that such keys and constraints can be effectively implemented outside the database. (Here is a subtle clue: they can’t.) • Relationships: We will briefly survey the different types of relationships that can exist between relational tables. • SQL: We will discuss the need for a single, standard, set-based language for interrogating relational databases. • Dependencies: Finally, we will discuss the concept of dependencies between values and how they shape the process of designing databases later in the book. As a side effect of this discussion, we will reach agreement on the meaning of some of the important terms and concepts that will be used throughout the book when discussing and describ- ing relational databases. Some of these terms are misunderstood and misused by a large number (if not a majority) of people. If we are not in agreement on their meaning from the beginning, then eventually you might end up wondering what the heck we’re talking about. In other words, it is important that we get on the same page when it comes to the concepts and basic theories that are fundamental to proper database design. Database Design Phases Too often when I sit down to build a system that requires data storage, the knee-jerk reaction is to start thinking in terms of how to fulfill an immediate need. Little regard is given to the future data needs, and even less is given to the impact the design will have on future business needs, reporting requirements, and, most crucial of all, the integrity of the data. I admit it is easy to succumb to quick versus good when you have a manager breathing coffee breath on the back of your neck every 10 minutes asking, “Done yet?” The problem with this mind-set is that obvious things are commonly missed, and late in the project you have to go back and tweak (and re-tweak) the design, and often tweaking means adding several major tables, splitting tables apart, or even nearly starting over (assuming there is time ). Too often, too much time is spent deciding how to build a system as quickly (and cheaply!) as possible, and too little time is spent considering the desired outcome. Clearly, the goal of any organization is to work efficiently, but it is still important to get things as right as possible the first time, partly because of the shackles of backward compatibility, which makes it harder and harder to actually make changes to your systems as more users exist. Maintenance programming is far more expen- sive (and an order of magnitude less fun) than initially creating the system.
Đồng bộ tài khoản