HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [262]
If your database allows duplication, this type of mistake will happen all the time.
In general, you don’t want to enter anything into a database more than once. If you have a way to enter the Septic Slime Master one time, that should eliminate this type of problem.
Fields that change
Another kind of problem is evident in the Age field. (See, even superheroes have a mandatory retirement age.) Age is a good example of a field that shouldn’t really be in a database because it changes all the time. If you have age in your database, how are you going to account for people getting older? Do you update the age on each hero’s birthday? (If so, you need to store that birthday, and you need to run a script every day to see whether it’s somebody’s birthday.) You could just age everybody once a year, but this solution doesn’t seem like a good option, either.
Whenever possible, you want to avoid fields that change regularly and instead use a formula to generate the appropriate results when you need them.
Deletion problems
Another kind of problem is lurking right under the surface. Say that you have to fire the Binary Boy. (With him, everything is black and white. You just can’t compromise with him.) You delete his record, and then you want to assign another hero to fight Octal. When you delete Binary Boy, you also delete all the information about Octal and his nefarious scheme.
In a related problem, what if you encounter a new villain and you haven’t yet assigned a hero to this villain? The current data design doesn’t allow you to add villains without heroes. You have to make up a fake hero, and that just doesn’t seem right.
Introducing Entity-Relationship Diagrams
You can solve all the problems with the database shown in Table 3-1 by breaking the single table into a series of smaller, more specialized tables.
The typical way of working with data design is to use a concept called an Entity-Relationship (ER) diagram. This form of diagram usually includes the following:
♦ Entities: Typically, a table is an entity, but you see other kinds of entities, too. An entity is usually drawn as a box with each field listed inside.
♦ Relationships: Relationships are drawn as lines between the boxes. As you find out about various forms of relationships, I show you the particular symbols used to describe these relationship types.
Using MySQL Workbench to draw ER diagrams
You can create ER diagrams with anything (I typically use a whiteboard), but some very nice free software can help. One particularly nice program is called MySQL Workbench. This software has a number of really handy features:
♦ Visual representation of database design: MySQL Workbench allows you to define a table easily and then see how it looks in ER form. You can create several tables and manipulate them visually to see how they relate.
♦ An understanding of ER rules: MySQL Workbench is not simply a drawing program. It’s specialized for drawing ER diagrams, so it creates a standard design for each table and relationship. Other data administrators can understand the ER diagrams you create with this tool.
♦ Integration with MySQL: Once you’ve created a data design you like, you can have MySQL Workbench create a MySQL script to create the databases you’ve defined. In fact, you can even have Workbench look at an existing MySQL database and create an ER diagram from it.
Creating a table definition in Workbench
Creating your tables in MySQL Workbench is a fairly easy task:
1. Create a new model.
Choose File⇒New to create a new model. Figure 3-1 shows the MySQL Workbench main screen.
Figure 3-1: MySQL Workbench main screen.
2. Create a new table.
Use the Add Table icon to create a new table. A new dialog box opens at the bottom of the screen, allowing you to change the table name. You see a new table form like the one in Figure 3-2. Change the table name to ‘hero’ but leave the other values blank for