Back to description
Using modern database tools, just about anyone can build a database. The question is, will the resulting database be useful... more
Using modern database tools, just about anyone can build a database. The question is, will the resulting database be useful?
A database won’t do you much good if you can’t get data out of it quickly, reliably, and consistently. It won’t be useful if it’s full of incorrect or contradictory data. It also won’t be useful if it is stolen, lost, or corrupted by data that was only half written when the system crashed.
You can address all of these potential problems by using modern database tools, a good database design, and a pinch of common sense, but only if you understand what those problems are so you can avoid them.
Step one in the quest for a useful database is understanding database goals. What should a database do? What makes a database useful and what problems can it solve? Working with a powerful database tool without goals is like flying a plane through clouds without a compass: you have the tools you need but no sense of direction.
This chapter describes the goals of database design. By studying information containers such as files that can play the role of a database, it defines properties that good databases have and problems that they should avoid.
In this chapter, you learn:
Why a good database design is important.
Strengths and weaknesses of different kinds of information containers that can act as databases.
How computerized databases can benefit from those strengths and avoid those weaknesses.
How good database design helps achieve database goals.
What CRUD and ACID are, and why they are relevant to database design.
... less
Recall the question posed at the beginning of Chapter 1: What is a database? The answer given there was:... more
Recall the question posed at the beginning of Chapter 1: What is a database? The answer given there was:
A database is a tool that stores data, and lets you create, read, update, and delete the data in some manner.
This broad definition allows you to consider all sorts of odd things as databases including notebooks, filing cabinets, and your brain. If you’re flexible about what you consider data, this definition includes even stranger objects such as a chess set (which stores board positions) or a parking lot (which stores car types and positions, although it might be hard for you to update any given car’s position without the owner’s consent).
This chapter moves into the realm of computerized databases. Relational databases are by far the most commonly used computerized databases today and most of this book (and other database books) focus on them, but it’s still worth taking some time first to learn a bit about other kinds of computerized databases that are available. Relational databases are extremely useful in a huge number of situations but they’re not the only game in town. Sometimes a different kind of database may make more sense for your particular problem.
Before you start frantically throwing tables together, building indexes, and normalizing everything in sight, it’s worth taking some time to study some of the other kinds of databases that are available.
This chapter describes different types of databases including flat files, spreadsheets, hierarchical databases (XML), object databases, and relational databases. Relational databases are the most common of these but this chapter describes the others and gives some tips on deciding whether one of the others would be more appropriate.
What kinds of databases are most common.
The strengths and weaknesses of these database types.
How to decide which kind of database to use.
The previous chapters discussed databases in general terms. Chapter 1 explained the general goals of database design. Chapter... more
The previous chapters discussed databases in general terms. Chapter 1 explained the general goals of database design. Chapter 2 described some of the many kinds of databases that you might decide to use.
With this chapter the book starts to focus on a particular kind of database: the relational database. Relational databases are very powerful, are the most commonly used kind of database in computer applications today, and are the focus of the rest of this book.
Before you can start learning how to properly design a relational database, you must understand the basic concepts and terms that underlie relational databases.
This chapter provides an introduction to relational databases. It explains the major ideas and terms that you need to know before you can start designing and building relational databases.
In this chapter, you learn about relational database terms such as:
Table and relation
Record, row, and tuple
Column, field, and attribute
Constraint, key, and index
Finally, you learn about the operations that you can use to get data out of a relational database.
The previous chapters discussed databases in general terms. Chapters 1 and 2 explained the goals of database design and described... more
The previous chapters discussed databases in general terms. Chapters 1 and 2 explained the goals of database design and described some of the types of databases that are available. Chapter 3 described the most common type of database, relational databases, in slightly greater detail. With this basic understanding of databases, you’re ready to take the first step in designing an actual database to solve a particular problem: understanding the user’s needs.
Designing any custom product, whether it’s a database, beach house, or case mod (see www.neatorama.com/case-mod/index.php for some amazing examples), is largely a translation process. You need to translate the customers’ needs, wants, and desires from the sometimes fuzzy ideas floating around in their heads into a product that meets the customers’ needs.
www.neatorama.com/case-mod/index.php
The first step in the translation process is understanding the user’s requirements. Unless you know what the user needs, you cannot build it. Designing the best order processing database imaginable won’t do you a bit of good if the customer really wants a circuit design database or an ostrich race handicapping system.
Just as the database design forms the foundation upon which the rest of the application’s development stands, your understanding of the user’s needs form the foundation of the database design. If you don’t know what the user needs, how can you possibly design it?
If you don’t understand the customer’s needs thoroughly and completely, you may as well pack it in now. There’s little satisfaction in wasting months of your life and a pile of your company’s money to build something unusable. Make sure you’re on the right road before you stomp on the accelerator and burn rubber down a dead-end alley.
This chapter explains techniques that you can use to learn about the customer’s needs. It describes methods that you can use to record those needs in a concrete and verifiable way.
The sections that follow describe some of the steps you can take to better understand the customers’ needs. In some projects, you may not need to follow all of these steps. For example, if your customer is a single person with very concrete ideas about what needs to be done, you may not need to spend much time learning who’s who or brainstorming. If your customer works with government classified data, you may not be allowed to “walk a mile in the user’s shoes” and you may have access to only some of the business’s documentation.
I once knew a developer who was working on a classified project. He had clearance to see the source code but not the data, so every week his customer brought him a giant printout of the latest run with all of the data carefully clipped out with scissors. He would try to guess what was going on and make some suggestions so the customer’s developers could try to fix the code. Then the cycle repeated the next week. What an odd way to work!
In other projects, the steps may work best in a different order. You may find it better to brainstorm before visiting the customers’ site and watching them work.
These are just steps that I’ve found most useful in trying to understand the customers’ situation. You’ll have to adjust them as necessary to fit each of your projects.
In this chapter, you learn how to:
Understand the customers’ needs and motivations.
Gather and document user requirements.
Cull requirements from existing practices and information.
Build use cases to understand the user’s needs and to measure success or failure.
Anticipate changes and future needs to build the most flexible database possible.
After you master these techniques, you’ll be ready to move on to the next step and actually start designing the database.
Chapter 4 discussed ways you can work with customers to gain a full understanding of the problem at hand. The result should... more
Chapter 4 discussed ways you can work with customers to gain a full understanding of the problem at hand. The result should be a big pile of facts, goals, needs, and requirements that should be part of the new database and its surrounding ecosystem. You may already have made some connections among various parts of this information, but mostly it should be a big heap of requirements that doesn’t say too much about the database’s design and construction.
This kind of pile of information is sometimes called a contextual list. It’s basically just a list of important stuff (although it may be fairly elaborate and include requirements documents, diagrams, charts, and all sorts of other supporting documentation).
The next step in turning the conceptual list into a database is converting it into a more formal model. You can compare the formal model to the contextual list and make sure that the model can handle all of your requirements.
You can also use the model to verify that you’re on track. You can explain the model to the customers and see if they think it will handle all of their needs or if they forgot to mention something important while you were following the procedures described in Chapter 4.
Constantly verifying that you’re on track is an important part of any project. It’s much easier to hit a target if you’re constantly checking the map and making any necessary adjustments. You wouldn’t aim your car at a parking space, close your eyes, and step on the pedal, would you? It’s much easier to park if you keep an eye on your progress, the other cars, the skateboarders slamming nosegrinds off the curb, kids riding on shopping carts, and everything else in the parking lot.
After you build a data model (or possibly more than one), you can use it to build a relational model. The relational model is a specific kind of formal model that has a structure very similar to the one used by relational databases. That makes it relatively easy to convert the relational model into an actual database in Access, SQL Server, MySQL, or some other database product.
In this chapter you learn how to:
Create user interface models
Create semantic object models
Create entity-relationship models
Convert those types of models into relational models
After you master these techniques, you’ll be ready to start pulling the models apart and rearranging the pieces to improve the design by making it lean and flexible.
Chapter 5 explained how to build models to represent the entities involved in a database project and to study the interactions... more
Chapter 5 explained how to build models to represent the entities involved in a database project and to study the interactions among those entities. The final kind of model described in that chapter, the relational model, has a structure that closely mimics the organization of a relational database. You can easily convert a relational model into a working relational database.
Before you do, however, you should optimize the relational model to make the final database as flexible and efficient as possible. Optimizing the model now is easier than reorganizing the database later so it’s worth taking some time to make sure you get the database design right the first time.
The first step in optimizing the database is extracting business rules. Keeping business rules separate from other database constraints and relations, at least logically, makes later changes to the database easier.
In this chapter you learn:
Why business rules are important.
How to identify business rules.
How to modify a relational model to isolate business rules.
After you understand business rules, you’ll be able to use them to make the database more flexible and easier to maintain.
Chapter 6 explained how you can make a database more flexible and robust by extracting certain business rules from the database’s... more
Chapter 6 explained how you can make a database more flexible and robust by extracting certain business rules from the database’s structure. By removing some of the more complex and changeable rules from the database’s check constraints, you make it easier to change those rules later.
Another way to make a database more flexible and robust is to “normalize” it. Normalization makes the database more able to accommodate changes in the structure of the data. It also protects the database against certain kinds of errors.
This chapter explains what normalization is and tells how you can use it to improve your database design.
What normalization is.
What problems different types or levels of normalization address.
How to normalize a database.
How to know what level of normalization is best for your database.
After you normalize your relational model, you’ll be ready to build the database.
The previous chapters showed how to gather user requirements, build a database model, and normalize the database to improve... more
The previous chapters showed how to gather user requirements, build a database model, and normalize the database to improve its performance and robustness. Those chapters showed how to look at the database from the customers’ perspective, from the end user’s perspective, and from a database normalization perspective, but there’s one other point of view that you should consider before you open your database product and start slapping tables together: the programmer’s.
You may not be responsible for writing a program to work with a database. The database may not ever directly interact with a program (although that’s rare). In any case, the techniques that you would use to make a database easier for a program to use often apply to other situations. Learning how to help a database support software applications can make the database easier to use in general.
Steps you can take to make the database more efficient in practical use.
Methods for making validation easier in the user interface.
Ways to easily manage non-searchable data.
This chapter describes several things that you can do to make the database more program-friendly.
A few of these ideas (such as multi-tier architecture) have been covered in earlier chapters. They are repeated in brief here to tie them together with other programming-related topics, but you should refer to the original chapters for more detailed information.
The previous chapters described general techniques for building database designs. For example, Chapter 5 explained how to... more
The previous chapters described general techniques for building database designs. For example, Chapter 5 explained how to build semantic object models and entity-relationship diagrams for a database, and how to convert those models into relational designs. Chapter 7 explained how to transform those designs to normalize the database.
This chapter takes a different approach. It focuses on data design scenarios and describes methods for building them in a relational model.
In this chapter you learn techniques for:
Providing different kinds of associations between objects.
Storing data hierarchies and networks.
Handling time-related data.
Logging user actions.
This chapter does not provide designs for specific situations such as order tracking or employee payroll. Appendix B, “Sample Database Designs,” contains those sorts of examples.
This chapter focuses on a more detailed level to give you the techniques you need to build the pieces that make up a design. You can use these techniques as the beginning of a database design toolbox that you can apply to your problems.
The following sections group these patterns into three broad categories: associations, temporal data, and logging and locking.
Chapter 9 described some common patterns that you may want to use while designing a database. This chapter takes an opposite... more
Chapter 9 described some common patterns that you may want to use while designing a database. This chapter takes an opposite approach: it describes some common pitfalls that you don’t want to fall into while designing a database. If you see one of these situations starting to sprout in your design, stop and rethink the situation so you can avoid a potential problem as soon as possible.
In this chapter you learn to avoid problems with:
Normalization and denormalization.
Lack of planning and standards.
Mishmash and catchall tables.
Performance anxiety.
The following sections describe some of the most common and troublesome problems that can infect a database design.
The first step in designing and building a database is gathering user requirements. You cannot build a database to solve... more
The first step in designing and building a database is gathering user requirements. You cannot build a database to solve the users’ needs unless you understand those needs. This chapter walks you through the process for The Pampered Pet.
In this chapter you see examples of:
Identifying user requirements.
Determining what the database’s main entities are.
Defining use cases to verify that requirements have been met.
The scenarios described here do not necessarily present the most efficient possible outcome. Ideally your customers know exactly what they need and give you their full cooperation while spelling out the requirements in crystal clear detail. Things don’t always go that way, however (in fact, I’ve never seen it happen that way), so neither do the steps described here.
Perhaps you’ll get lucky and things will go more smoothly than some of the discussions described here, but you should realize that at least sometimes people skills are as important as database design skills during this phase.
The previous chapter described requirements gathering for The Pampered Pet database project. It took the basic requirements... more
The previous chapter described requirements gathering for The Pampered Pet database project. It took the basic requirements and used them to build the fundamental entities that will take part in the database’s operations.
This chapter builds more formal data models describing those entities. Semantic object models emphasize the entities’ fields and entity-relationship diagrams emphasize the relationships among them.
Converting requirements entities into semantic objects.
Splitting off repeated data into new objects.
Converting requirements entities and semantic objects into entity-relationship diagrams.
Converting semantic object models and entity-relationship diagrams into relational models.
The previous chapters have built up a basic design for The Pampered Pet database. They gathered customer requirements, built... more
The previous chapters have built up a basic design for The Pampered Pet database. They gathered customer requirements, built a semantic object model and entity-relationship diagrams, and converted those into a relational model.
This chapter further refines the design by identifying business rules in the relational model and isolating them so they will be easy to modify in the future if necessary.
In this chapter you see examples that:
Identify required fields and other field-level constraints that are unlikely to change.
Identify sanity checks that are also unlikely to change.
Identify business rules that are more complicated or likely to change in the future.
Chapters 11 through 13 walked through the steps of designing a preliminary database for The Pampered Pet. They showed how... more
Chapters 11 through 13 walked through the steps of designing a preliminary database for The Pampered Pet. They showed how to gather requirements, build semantic object and entity-relationship models, and convert those into a relational model. Chapter 13 showed how to identify rules that should be built into the database and more complex or changeable rules that should be isolated as business rules.
Even after all of this work, the database isn’t perfect. This chapter puts the finishing touches on the database by normalizing it appropriately.
Improving the design to make the database more flexible.
Identifying tables that are insufficiently normalized.
Normalizing tables to prevent data anomalies.
Not normalizing where normalization would be more trouble than it’s worth.
The chapters earlier in this book explained how to design a database. This chapter explains how to build a database in Microsoft... more
The chapters earlier in this book explained how to design a database. This chapter explains how to build a database in Microsoft Access. In this chapter you learn how to:
Create tables.
Create foreign key constraints graphically.
Create check constraints that validate data.
(The examples in this chapter were tested in Microsoft Access 2007 with Service Pack 1.)
MySQL is a database engine designed to be used as a backend for a separate user interface. The user interface might be a... more
MySQL is a database engine designed to be used as a backend for a separate user interface. The user interface might be a Web page that uses Java, JavaScript, ASP, ASP.NET, or some other scripting technology to interact with the database. Alternatively the user interface might be a desktop application built in a high-level programming language such as Visual Basic, C#, or C++.
This chapter explains how to use MySQL to design and build a relational database. In this chapter you learn how to:
Create foreign key constraints.
Create entity-relationship diagrams.
Create triggers that validate data.
Export scripts that build a database.
Eventually you (or someone else) must actually build the database that you’ve designed. Also at some point, someone will... more
Eventually you (or someone else) must actually build the database that you’ve designed. Also at some point, someone will probably want to actually use the database you’ve spent so much time designing.
SQL (pronounced “sequel”) includes commands that let you build, modify, and manipulate a database. Chances are SQL will be used either directly or behind the scenes to create and use the database. Even tools such as MySQL, Access, and SQL Server that let you interactively build a database also allow you to use SQL.
SQL is also directly useful for creating and initializing a database. In fact, it’s so useful that it’s the topic of the next chapter.
SQL is such an important part of database development that your education as a database designer is sadly lacking if you don’t at least understand the basics. (The other developers will rightfully mock you if you don’t chuckle when you see a tee-shirt that says, “SELECT * FROM People WHERE NOT Clue IS null.”)
SELECT * FROM People WHERE NOT Clue IS null
In this chapter you learn how to use SQL to:
Create and delete tables.
Insert data into tables.
Select data from the database using various criteria and sort the results.
Modify data in the database.
Delete records.
The previous chapter provided an introduction to using SQL to create and manage databases. That chapter also hinted at techniques... more
The previous chapter provided an introduction to using SQL to create and manage databases. That chapter also hinted at techniques for using SQL scripts to make database maintenance easier.
This chapter goes a little further. It discusses some of the details that you need to take into account when you use scripts to manage a database.
Know when scripts can be useful.
Build tables in a valid order.
Insert data into tables in a valid order.
Drop tables in a valid order.
At this point, you’ve learned how to identify customer needs, design a database, refine the design, and implement the database... more
At this point, you’ve learned how to identify customer needs, design a database, refine the design, and implement the database interactively or by using scripts. Even after you start using the database, however, the work isn’t done. You (or someone) must perform regular maintenance to keep the database healthy and efficient.
Like a high-performance sports car, the database needs regular maintenance to keep it running at peak efficiency. Just as the best engineering and construction in the world won’t save your engine if you drive 100,000 miles without an oil change, your database design won’t give you optimal performance if you don’t give it regular tune-ups. (At least the database doesn’t need collision insurance.)
This chapter describes some of the maintenance chores that must be performed to keep the database working smoothly. Unfortunately the details of performing these chores differ greatly in different databases so the exact steps you need to perform are not included here. Instead this chapter describes the issues that you should keep in mind when you design the database’s maintenance schedule. You should consult the documentation for your particular database product to flesh out the details.
What tasks are necessary to keep a database functional.
How to schedule backups to safeguard data.
What you can do to keep a database working efficiently.
Like database maintenance, database security is an important topic with details that vary from database to database. This... more
Like database maintenance, database security is an important topic with details that vary from database to database. This chapter doesn’t try to cover everything there is to know about database security. Instead it explains some of the general concepts that you should understand.
Pick a reasonable level of security for the database.
Choose good passwords.
Give users necessary privileges.
Promote a database’s physical security.
When you break a data model down into small pieces, there are really only three types of data relationships: one-to-one,... more
When you break a data model down into small pieces, there are really only three types of data relationships: one-to-one, one-to-many, and many-to-many (modeled with to one-to-many relationships). If you think in those terms, then you really don’t need examples. Just break the problem into small enough pieces and start assembling these three kinds of relationships. (Inheritance and subtyping forms another kind of logical relationship that you can also model.)
However, it may be useful to see more complete examples that include several different entities associated in typical ways. This appendix is intended to show you those kinds of examples.
Note that many different problems can be modeled in very similar ways. For example, consider a typical library. It has one or more copies of a whole bunch of books and lends them to patrons for a specific amount of time. Patrons can renew a book once and pay late fees if a book isn’t returned on time.
Now consider a business that rents party supplies such as tables, chairs, big tents, dunk tanks, doves, and so forth. Like a library, this business has multiple copies of many of these items (it probably has dozens of tables and hundreds of chairs). Also like a library, this business “loans” (for a fee) its items to customers and charges a late fee if an item isn’t returned on time.
Though a library and a party rental store are very different organizations, the structure of their databases is quite similar.
As you look at the examples in this appendix, think about variations that might use a similar structure. Though your application may not fit these examples exactly, you may find an example that uses a similar structure.
Also note that different applications might use very different database designs for the same data. The exact fields and sometimes even the tables included in the design depend on the application’s focus.
For example, consider a large company’s employee data. If you’re building an application to assign employees to tasks for which they are qualified, your database will probably have an EmployeeSkills table that matches employee records to their skills. You’ll also need a Tasks table that describes tasks and lists the skills that they require.
In contrast, suppose you need to build a human resources application that tracks employee payroll deductions for retirement contributions, medical coverage, and so forth. Although this application deals with the same employees, it doesn’t need skill or task data so it doesn’t need the EmployeeSkills or Tasks tables. It also needs new employee data not required by the work assignment project such as employee Social Security number, bank account number, next of kin, and anniversary date.
These two applications deal with the same physical entities (employees) but have very different data needs.
Because the types of data that you might need to store in a particular table depends on your application, these examples don’t try to be exhaustive. For employee data, a table might include a few fields such as FirstName, LastName, and HireDate to give you an idea of what the table might include but you’ll have to fill in the details for your application.
The following sections each describe a single example database design and give some ideas for variations that you may find useful. In particular, many of these models can include all sorts of address information such as Street, Suite, Building, Office, MailStop, City, State, Zip, PostalCode, Phone, and Extension. To keep the models simple, some tables include a single Address entry to represent address information. You should use whatever address information is appropriate for your application.
Purchase Before purchasing this product, please be sure you have met all software and system requirements, and that you understand any limits placed upon its use.
Return Policy Wrox Chapters on Demand are non-returnable and non-refundable.
Reader Software Wrox Chapters on Demand are offered as PDFs, and they must be viewed using the Adobe Reader. If you do not have the Reader installed, it can be downloaded for free at Adobe.com.
Test Download As Wrox Chapters on Demand purchases are non-returnable, it is advisable that you test your system and software configurations with a free sample download before you place an order.
Usage Rights for a Wrox Chapter on Demand File Any Wrox Chapter on Demand product you purchase from this site will come with certain restrictions that allow Wiley to protect the copyrights of its products. After you purchase and download this title, you:
If you have any questions about these restrictions, you may contact Customer Care at (877) 762-2974 (8 a.m. - 5 p.m. EST, Monday - Friday). If you have any issues related to Technical Support, please contact us at 800-762-2974 (United States only) or 317-572-3994 (International) 8 a.m. - 8 p.m. EST, Monday - Friday).
