Channels


INFO-LINK



Architecture & Design

Relational Databases 101

By William R. Vaughn with Peter Blackburn, October 20, 2006

0 Comments

Understanding what relational databases are all about is the first step to effectively using them.

Many of my readers come from backgrounds that don't include formal training on the best ways to design and create efficient, business-class relational databases. If you arrive here with Microsoft Access or FoxPro experience, you're at an advantage—you know that, for the most part, the process of creating a database is hidden from you by the application's IDE—you just use drag-and-drop or use wizards to build the databases and tables you want. That's not at all bad, but without an in-depth understanding of how to best create, tune, and protect a relational database, I suspect that the relational "normalcy," relational integrity constraints, performance, and scalability of the result might not be particularly stellar. And, more importantly, the data might not be particularly secure. By "normalcy," I mean how well the database conforms to the recognized standards of relational design where database designers attempt to "normalize" databases to at least the third level. If you're not sure how to do this, or even what this means, have no fear—I'll explain this later. The SQL gurus with whom I work (like Peter Blackburn, Kimberly Tripp, and a litany of others) are convinced that more problems can be solved by efficient database design than by the cleverest, best-written application front-end.

IMHO - It's not how fast you ask the question—it's how long it takes to find the answer that gates performance.

Getting Started with Solid Database Design

The Microsoft Books Online (BOL) documentation seems to fall a bit short in this important subject, so this chapter might be helpful for those who need a more complete understanding of how to create a best-practice relational database. The problem faced by any database designer is knowing what's going to be stored ahead of time—before the first table is created. That's always been (and always will be) a problem. As I've said before, a customer rarely knows what they want until they don't get it.

To get started on the right foot, I recommend a good course in relational theory like Extended Relational Analysis. This can do a world of good—but its depth is well beyond the scope of this book. In courses like this, you learn how to ask the right questions for each "entity" you expect to store in the database.

I also think that using a (big) whiteboard to lay out the database with your team (or customer) can help visualize the data. Getting everyone who is going to consume the data is essential. How I design a database for a single-focused project is very different than the way it's designed for projects where a small multitude of groups expect to consume the data. Admittedly, database development by committee is tough, and one should try to avoid those situations, but leaving town might not be an option.

Before we get into the academics of normalization, let's spend a few moments in quiet contemplation and focus on a few guiding principles. As you design your database, you should keep these basic tenants in mind:

  • Each table needs a unique identifier. That is, you need to choose one or more columns to permit SQL Server to find specific rows to return or update without including other irrelevant rows. In SQL Server, this typically means each table should have an "ID" column (typically cast as an Identity integer) that gives the row a unique (SQL Server–generated) value. You should define this column using the Primary Key (PK) constraint (as discussed in Chapter 2, "How Does SQL Server Work?"). For example Au_ID is the unique identifier for the Authors table in the sample Biblio database.
  • Each table should store only one kind of information and not repeat information in multiple columns. As I discuss next, this is where normalization comes in. SQL Server is tuned to work with small, tight rows that contain relatively few columns. If you find your table has more than a dozen columns, you're treading off the boards and into the swamp. Remember, the largest row you can define is only 8K (not counting BLOB columns).
  • Microsoft feels that you should avoid columns that can be set to NULL—I'm not so sure. That is, they feel that you should avoid columns where you might not have access to the data at all and cannot (logically) assign an arbitrary default. Each time you define a column as permitting NULL (making it "nullable"), SQL Server incurs extra overhead. It makes sense to move these columns to another table and cross-reference them to the table's PK as long as the database complexity does not get out of hand.
  • Each column needs to be defined both with the content in mind and with the constraints needed to keep it pure. It's not enough to type a column as integer and hope that the data entered therein is going to be pure. All columns, especially numeric columns, need to have (at least) CHECK constraints defined, if not TSQL rules. Columns should be defined to hold what's expected to be saved—and no more. Needlessly bloating data type capacity simply chews up memory to no good purpose. If you have columns that contain text, but that text is never expressed in Unicode, don't use a Unicode type. If you have a date column but don't need to store time with 3.33-millisecond accuracy, use smalldatetime instead of datetime. You get the idea. In this case, less is more—more space saved, more memory available to store other pertinent stuff. I'll show you how much each column costs in memory near the end of this chapter.

IMHO - Understand that all data is evil until proven innocent—it's not in the U.S. Constitution, but given the state of the current Congress, it just might get there.

  • Start thinking about a concurrency strategy from the beginning. Determine how data is to be shared (if at all). Consider that many "single-user" databases are doomed to failure once they are "converted" to multi-user. Think about the mechanism you're planning to use to determine if a row has changed once it's fetched. For example, you might (perhaps ought) to use a "Rowversion" or "TimeStamp" column to help track access—it can make Visual Studio's job a lot easier (and yours, too) when it comes time to write action commands to change the database.
  • Avoid BLOBs in the database. I have been suggesting this for over a decade, and those who have listened have been able to build a smaller, faster, and simpler database. If you have BLOBs, store them in files (or on RO media) and use the database to store the path.
  • Finally, and perhaps most importantly, for less-experienced developers, I think that you should strive to keep your database simple—simple to understand, support, and maintain. Excessive complexity is the bane of many a mature and amateur database designer.

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 Next Page >>

Around the Web

CoreDet: A Compiler and Runtime System for Deterministic Multithreaded Execution

CoreDet is a fully automatic compiler and runtime system for deterministic execution of arbitrary C/C++ multithreaded programs.

Quick Read

Honeypot Detection in Advanced Botnet Attacks

Honeypots have been successfully deployed in many computer security defense systems.

Quick Read

Swarm: A True Distributed Programming Language

The Swarm prototype is a simple stack-based language, akin to a primitive version of the Java bytecode interpreter.

Quick Read

Key Software Development Trends

Several trends are emerging within the area of software development. Here are some of the most important trends S. Somasegar has been thinking about recently.

Quick Read

Understanding Parallel Performance

Understanding parallel performance. How do you know when good is good enough?

Quick Read

Short and Tweet: Experiments on Recommending Content from Information Streams

The authors used 12 algorithms to study the URL recommendation on Twitter as a means of better directing attention in information streams.

Quick Read





Recent Articles

Video

Forty finalists will gather in Washington, D.C. from March 11-16 to compete for $630,000 in awards.; DDJ; Intel; science; Dr. Dobb's talks with Commonsware's Mark Murphy about what's involved in developing software for the Android operating system; Android; apple; DDJ; tablet development; The new method uses analytics technology developed by the Mayo and IBM collaboration, Medical Imaging Informatics Innovation Center, and has proven a 95 percent accuracy rate in detecting aneurysm.; Algorithm; DDJ; diagnostics; ibm; imaging; T-Mobile USA is enabling phone calls to Haiti without charges for international long distance through January 31 and retroactive to the earthquake on January 12; DDJ; mobile; wireless; Al Williams gives you a demor of One-Der: The One Instruction CPU; DDJ; At the 2010 International Consumer Electronics Show, the auto industry's first working smartphone application was unveiled; DDJ; mobile; The Bluetooth Special Interest Group (SIG) has announced the adoption of BLUETOOTH low energy wireless technology.; bluetooth; DDJ; wireless; IBM has unveiled its list of five innovations that have the potential to change how people live, work and play in cities around the world over the next five to ten years; DDJ; ibm; TeliaSonera's LTE mobile broadband commercial network in Stockholm is now the fastest and largest in the world.; broadband; DDJ; ericsson; mobile; Google has introduced, google Goggles, a visual search application on Android devices that allows users to search for objects using images rather than words; Android; DDJ; google; mobile; Visual Search Applications; Dr. Dobb's talks with David Intersimone, Vice President of Developer Relations and Chief Evangelist at Embarcadero Technologies, about RAD Studio 2010, SQL optimization and his reflections on the software industry.; database programming; DDJ; sql; Researchers from Intel Labs have created an experimental, 48-core Intel processor or "single-chip cloud computer."; cloud computing; DDJ; Intel; multicore; parallelism; The Large Hadron Collider will produce roughly 15 million gigabytes of data annually, to be accessed by a distributed computing and data storage infrastructure called the LHC Computing Grid.; CERN; DDJ; grid computing; physics; A mobile handheld device designed to let users can point, shoot and listen to printed text.; DDJ; Intel; mobile; Ericsson has become the first vendor to prove end to end interoperability in TD-LTE, another standard of 4G radio technologies designed to increase the capacity and speed of mobile telephone networks.; DDJ; ericsson; mobile; TD-LTE; According to a recent study, 80 percent of US respondents feel there are unspoken rules about mobile technology usage, and approximately 69 percent agreed that violations of these unspoken mobile manners are unacceptable.; DDJ; Intel; mobile; IBM and Canonical will introduce a software package for netbooks and other thin client devices in Africa. This is the first cloud- and premise-based Linux netbook software package offered by IBM and Canonical.; cloud computing; DDJ; ibm; His unprecedented ability to manipulate individual atoms signaled a quantum leap forward in in nanoscience experimentation and heralded in the age of nanotechnology.; DDJ; ibm; nanotechnology; IBM honored for its invention of the Blue Gene family of supercomputers. Adobe founders also recognized.; adobe; DDJ; ibm; Former U.S. President Bill Clinton addressed thousands of online entrepreneurs from around the world gathered for the third APEC Business Advisory Council SME Summit in Hangzhou, China.; DDJ; e-business; With free cooling for several months a year, Sweden is an ideal location for cost-efficient data centers.; data centers; DDJ; PNC Bank introduces a new mobile App for the iPhone and iPod touch that provides Virtual Wallet customers with a high-def view of their money while on the go.; DDJ; iphone; The Swedish LTE site will be part of a commercial network scheduled to go live in 2010, bringing data rates far above what is possible in today's mobile broadband networks.; DDJ; ericsson; mobile broadband; Nanotechnology advancement could lead to smaller, faster, more energy efficient computer chips.; circuit boards; DDJ; nanotech; semiconductor; Dr Dobbs talks with with Claudia Backus, Senior Director of Ecosystem Programs at Motorola, regarding the company's recently released MotoDEV Studio for their Android-powered phones.; Android; DDJ; mobile; motodev; The Extremadura Regional Government of Spain and IBM have launched an electronic prescription system in 680 pharmacies in western Spain.; DDJ; ibm; Ericsson to Acquire Majority of Nortel's North American Wireless Business; DDJ; ericsson; mobile; telecom; Nintendo's Wii Sports Resort is an immersive, expansive active-play game that includes a dozen resort-themed activities.; DDJ; nintendo; video games; OnStar can remotely send a signal to the electronic system in the subscriber's stolen vehicle and the vehicle will not be able to be re-started.; cellular; DDJ; wireless; In celebration of the historic Apollo Moon landing, Google has released Moon in Google Earth.; DDJ; google; Ericsson has been awarded contracts with the three telecom operators in China to provide fixed broadband access.; broadband; DDJ; mobile; tv; wireless; Dr. Dobb's talks with Adobe's Adam Lehman about the upcoming release of ColdFusion specifically optimized for Flash and Adobe AIR platform delivery.; adobe; ColdFusion; DDJ; eclipse; Companies team to develop computing device and chipset architectures that will combine the performance of powerful computers with high-bandwidth mobile broadband communications and ubiquitous Internet connectivity.; broadband; DDJ; Intel; mobile; nokia; Adobe Systems and HTC recently announced that the new HTC Hero will be the first Android phone to ship with support for Adobe Flash Platform technology.; adobe; Android; cell phones; DDJ; flash; mobile; mobility; 3.2 million Euros awarded across eight prize categorie recognizing world-class scientific research and artistic creation.; DDJ; A parody of Paul Simon's "50 Ways to Leave Your Lover," but for software security nerds.; DDJ; sql; Dr. Dobb's Mike Riley talks with Jim Manias of Advanced Systems Concepts.  In this conversation, Jim discusses the new ActiveBatch 7 and how it can provide significant productivity gains for application developers and business process owners alike.; ActiveBatch; DDJ; Sun cofounder Scott McNealy and Oracle CEO Larry Ellison discussed Java's role in computing. Sun has also released OpenSolaris 2009.06.; DDJ; java; opensolaris; oracle; sun; Spotlight on NATO's centre of excellence on cyber defense in Tallinn, Estonia.; cyber defense; DDJ; nework security; security; Create Data Access Layers in ASP.NET; DDJ; In this demonstration you will learn how to layout a WPF application. We will explore the major layout panels that come with WPF, contrasting them with each other and describing when to use each.; DDJ; web development; windows; wpf; The Intel Foundation has announced the top winners of the Intel International Science and Engineering Fair; DDJ; Intel; News; science; Matt Hester demonstrates Internet Explorer’s 8 new feature Selectors API for utilizing CSS selectors for quick and easy element lookups.; DDJ; IE8; microsoft; windows; The NATO Virtual Silk Highway provides affordable, high-speed Internet access via satellite to the academic communities of the Caucasus and Central Asia.; DDJ; On a Windows Mobile device, applications are typically not closed down, but they stay in the background. Maarten Struys shows you a simple way to preserve battery power inside your own applications.; DDJ; microsoft; power consumption; windows; Windows Mobile Devices; Cadillac is now offering wireless Internet access with its CTS sedan.; DDJ; wireless broadband; By default, Windows Mobile Standard (Smartphone) applications launched from Visual Studio are not accessible on the device/emulator once they are minimized. In this video, Jim Wilson demonstrates two simple techniques to solve the problem.; DDJ; microsoft; smartphone; VIsual Studio; Mike Riley talks with the brass from Everypoint, creators of the NEMO mobile application development platform.; DDJ; Developers; development environments; mobile applications; Symmetric and asymmetric encryption algorithms, the SHA256 hash encryption algorithms, and how to implement in a simple application using Microsoft's Azure Services Platform.; Azure; DDJ; encryption; microsoft; security; windows; T-Mobile has introduced the Sidekick LX, which features enhanced video capability.; DDJ; Mobile Smartphone; Bluetooth 3.0 offers speedier transmission of large amounts of video, music and photos between devices wirelessly.; bluetooth; DDJ; mobile networks; wireless broadband; Cities around the world are battling with stressed transportation networks, so IBM has announced plans for three new smart rail projects in China, Taiwan and The Netherlands.; DDJ; ibm; ILOG; CASMOBOT is a Nintendo Wii remote controlled slope lawn mower.; DDJ; Denmark; nintendo wii; research; robotics; Project ensures documents, images, video and other Internet-based data growing at over 100 terabytes per month will live on for future generations; data storage; DDJ; history; Intenet; research; Sun Microsystems; Dr. Dobb's talks with Dave McAllister, Director of Standards and Open Source for Adobe, about the Open Screen Project.; adobe; DDJ; Open Screen Project; open source; The Facebook Connect SDK provides the code to let third-party developers embed hooks into their applications so users can connect to their Facebook accounts and exchange information using iPhone apps.; apple; cocoa; DDJ; Facebook; iphone; Mars in Google Earth Updated; DDJ; google; google earth; Google mars; red planet; The Sun Cloud is built on the Sun Open Cloud Platform that leverages the best in world-class open source technologies. The Sun Open Cloud Platform brings together Java, MySQL, OpenSolaris and OpenStorage.; cloud computing; DDJ; java; open solaris; sun; DDJ; High School; Intel; science; ILOG Elixir is a suite of professional user interface controls that gives developers a rich collection of innovative and interactive data display components for Adobe Flex and Adobe Air.; adobe; air; DDJ; elixir; flash; flex; ILOG; The inaugural San Diego Science Festival being held this month is touted as one of the largest multicultural, multigenerational, multidisciplinary celebrations of science ever seen on the West Coast; DDJ; lockheed; News; science; IBM has announced Innov8 version 2, a new version of its serious game that helps students and professionals hone their business and technology skills in a compelling, familiar video game format.; DDJ; ibm; serious games; Swiss Automobile Visionary Frank M. Rinderknecht builds a concept car with adaptive energy concept and iPhone controls.; apple; Concept Car; DDJ; iphone; j; siemens; Two-Year Plan to Focus on 32 Nanometer Manufacturing Technology; 32 nanometer technology; chip; cpu; DDJ; gpu; Intel; manufacturing; Nehalem; Westmere; New version features ocean layer, historical imagery, and more.; DDJ; google; Dr. Dobb's talks with Marty Alchin, author of "Pro Django" about his book and the deep internals of the Django framework.; DDJ; Django; A new content-authoring solution for learning professionals; adobe; DDJ; toolkits; web authoring; In a Second Life setting, Danny Coward discusses Java FX with Dr. Dobb's Jon Erickson.; DDJ; java; JavaFX; sun; The Core i7 processor is the first member of a new family of Nehalem processor designs with new technologies that boost performance on demand.; chip; DDJ; Intel; processors; Dan Diephouse, creator of XFire, a high-performance open-source SOAP framework (which became the Apache CXF project), shares the five common mistakes in SOA governance and insight about the Apache CXF and Mule RESTpack development environments.; apache; Apache CXF; DDJ; mule; open source; soa; soap; Xfire; Adrian Kaehler and Gary Bradski discuss the Open Computer Vision Library (sourceforge.net/projects/opencvlibrary/) and their book "Learning OpenCV".; DDJ; Open Computer Vision Library; OpenCV; In the first part of this two-part interview, Stephen Wolfram reflects on the 20-year anniversary of Wolfram Research.; DDJ; Mathematica; Mathematics; science; In the second part of this two-part interview, Stephen Wolfram discusses his book "A New Kind of Science."; DDJ; Mathematica; Mathematics; science; Nick Hodges talks about Delphi 2009, a RAD tool for Windows, and Delphi Prism, a database engine for Windows, Mac OS X, and Linux.; DDJ; delphi; RAD; windows; Dr. Dobb's talks with Tony Lombardo, lead Technical Evangelist at Infragistics, about all new UI tools for Windows and .NET.; .net; DDJ; silverlight; ui; windows; wpf; Dr. Dobb's talks with Eric Schulz about his International Mathematica User's Conference 2008 presentation on the Mathematica Essentials Palette and the future digital educational material; DDJ; Mathematica; Mathematics; Dr. Dobb's talks with ActiveState's Trent Mick about the recently released Komodo IDE 5.0.; DDJ; ide; open source; Dr. Dobb's talks with Continuity Logic's Kris Carlson about "Why We Die: Simulation of the Evolution of Senescence" and why he programs with Mathematica's functional programming language.; DDJ; functional programming; Mathematica; simulation; Ericsson collaborates with Intel; DDJ; ericsson; Intel; Mobile technology; Dr. Dobb's talks with Schoeller Porter about the grid and cloud versions of Mathematica; clouds; DDJ; Grid; Mathematica; Dr Dobb's interviews Yehuda Katz, maintainer of the Merb project, about the advantages this highly optimized Ruby on Rails alternative offers to web application developers.; DDJ; Ruby on Rails; Dr. Dobb's talks with Thomas Roman, Professor of Mathematics at Central Connecticut State University, about "Mathematica Visualization in a Theoretical Physics Problem - Negative Energy in an Unusual Quantum State."; DDJ; Mathematica; physics; quantum; science; The Forbidden City: Beyond Space & Time is a fully immersive, three-dimensional virtual world that recreates a visceral sense of space and time.; Blade Server; China; DDJ; ibm; linux; mac; online; virtual world; windows; Dr. Dobb's interviews open source luminary Miguel de Icaza about his latest milestone of achieving Microsoft .NET 2.0 Framework compatibility with the Mono Project .; DDJ; Dr. Dobb/s interviews Paul Kimmel, author of "LINQ Unleashed for C#", about Microsoft's new query technology that lets developers poll any information from any data source regardless of location or structure. I; C#; DDJ; Dr. Dobb's; LINQ; microsoft; It takes a supercomputer to build a super car. ; DDJ; HPC; simulation; Dr. Dobb's shows how to install and execute cross-platform scripting languages on the Windows Mobile platform. In this installment, Mike Riley examines Perl for Windows Mobile devices.; DDJ; mobile devices; perl; windows; Dr. Dobb's shows how to install and execute cross-platform scripting languages on the Windows Mobile platform. In this installment, Mike Riley examines Python CE which is optimized for Windows Mobile devices.; DDJ; mobile devices; python; windows; Dr. Dobb's shows how to install and execute cross-platform scripting languages on the Windows Mobile platform. In this installment, Mike Riley examines Ruby for Windows Mobile devices.; DDJ; mobile devices; ruby; windows; Young participants at ITU TELECOM ASIA 2008 in Bangkok, Thailand received free laptops as part of ITU’s initiative to promote affordable devices to increase access to information and communication technologies.; communication; DDJ; itu; Currently technical strategist to Microsoft's Chief Software Architect, Rebecca Norlander has had a tremendous impact on Excel, Internet Explorer, Windows XP SP2, and Windows Vista Security. ; DDJ; microsoft; Contributing authors to the book "Beautiful Code" got together at Dr. Dobb's SD West Conference in March, 2008. Part 1 of 3.; DDJ; programming; software development; Contributing authors to the book "Beautiful Code" got together at Dr. Dobb's SD West Conference in March, 2008. Part 2 of 3.; DDJ; programming; software development; Contributing authors to the book "Beautiful Code" got together at Dr. Dobb's SD West Conference in March, 2008. Part 3 of 3.; DDJ; programming; software development; Anders Hejlsberg discusses C#, Turbo Pascal, and what it means to design a programming language. ; C#; DDJ; microsoft; Turbo Pascal; Solar powered laptops given to youths at ITU Asia 2008.; DDJ; News; telecommunications; IBM breakthrough stands to impact future direction of information technology.; DDJ; Mike Riley spoke to ActiveState's Jeff Hobbes about the new features in Tcl Dev Kit and Perl Dev Kit including the code coverage and hot-spot analysis tool and Mac OSX support.; DDJ; Tim O'Reilly addressed the OSCON convention in his Wednesday keynote titled "Degrees of Freedom, Open Source in the Wed 2.0 Era.; DDJ;
View All Videos

Most Popular

Stories Blogs

Most Recent Premium Content

Whitepapers