Lesson 10

This module offered a number of insights into the overall design process by identifying common design mistakes in four areas:

1. Business objects and rules
2. Constraints, columns, and keys
3. Relationships and referential integrity
4. International issues

It then offered tips on how to avoid them. It also defined the vital distinction between a database that is right and one that is useful.

Sometimes it’s tempting to build tables that contain unrelated values. The classic example is a Domain-Values table that contains allowed values for fields in tables scattered throughout the database. For example, suppose the State, Brand, and Medium fields take values from lists. State can take values CA, CT, NV, and so forth; Brand can take values MixAll, Thumb Master, and Pheidaux; and Medium can take values Oil, Acrylic, Pastel, and Crayon. You could build a DomainValues table with fields Table-Name, FieldName, and Value. Then it would hold records such as TableName = Artwork, FieldName = Medium, Value = Crayon. You would use this magic table to validate foreign keys in all of the other tables.
This approach will work, but it’s more of a headache than it’s worth. The table is filled with unrelated values and that can be confusing. It might seem that having one table rather than several would simplify the database design, but this single table does so many things that it can be hard to keep track of them all. Just think about drawing the database design’s ER diagram with this single table connected to dozens of other tables.
Tying this table to a whole bunch of others can make it a chokepoint for the entire system. It can also lead to unnecessary redundancy if multiple tables contain fields that have the same domains.
It is better to use separate tables for each of the domains that you need. In this example, just build separate States, Brands, and Media tables. Though this requires more tables, the pieces of the design are simpler, smaller, and easier to understand.
Remember the rule that one table should do one clearly defined thing and nothing else. Although this kind of mishmash table has an easily defined purpose, it does not do just one thing.

This module introduced you to the following terms:

1. business objects:Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.).
2. Business rules: A set of rules or conditions describing the business polices that apply to the data stored on a company databases.
3. column: Part of the structure of a database table; also known as a field or, during early database design, as an attribute.
4. constraints: Rules a database designer imposes upon certain elements in a database to preserve data integrity.
5. international issues: Database design considerations based on other countries\' differing data standards, alphabets, and writing systems.
6. Referential integrity: The means of maintaining the integrity of data between one or more tables that relate to each other, in other words, that a column of data in a table has a null or matching value in a corresponding table. Referential integrity is usually enforced with foreign keys
7. relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.