You saw in trying to resolve all the relationships among the tables in the database examples that you had to create another type of table, the join table. Four types of tables exist:
I've already discussed data tables and join tables at some length. I briefly discuss the other two types of tables so you can recognize them and know what they are.
Subset Tables and One-to-One Relationships
Occasionally, you'll find that you created a table that apparently represents a single subject, but for various reasons a group or subset of the records has several distinct fields.
For example, let's say you had a database of your music CDs. Most of the music genres representedjazz, country and western, folk rockconform to the same general table design, including fields such as Artist, Year Produced, and Music Label. But you also want to include your CDs of Broadway musicals. For the Broadway musicals, you might want to include the composer, the lyricist, the theater where the play opened, and so on.
Broadway musicals have composers and lyricists. Frank Sinatra, Miles Davis, and Metallica albums do not. So if you include Broadway CDs among the others, you're going to have a lot of records with several empty columns. A lot of empty fields don't usually cause major problems. But they can raise eyebrows among managers and users, who want to know just why so many records have so many empty fields.
Creating Subset Tables
Table 3.30 is the main table of music CDs. Tables 3.31 and 3.32 are subset tables for popular music and Broadway musical CDs. You create a subset table by taking the fields that are common to it out of the main table. For popular-music albums, this would include the artist and (perhaps) a Yes/No field for whether it's a collection of best hits. For Broadway musicals, the fields might include the composer, the lyricist, the theater in which the play originally ran, and so on.
Importantly, the fields that are common to both popular-music CDs and Broadway musicalsAlbum Name, Date Bought, Retailer, and so onremain in the original data table, as shown in Table 3.30.
Identifying One-to-One Relationships
The Music Discs table has a one-to-one relationship with both of the subset tables. In other words, a DiscID can appear only once in the Music Discs table and once in a subset table. The DiscID is the primary key of the Music Discs table. It is both a primary key and a foreign key in the Popular Music table and the Broadway Musicals table. The DiscID must exist in the Music Discs table to be included in a subset table.
In the sidebar "What Does Data Integrity Mean?", in the previous chapter, I mentioned that business rules put limitations on your database because of the nature of the organization and the way it conducts its operations. For example, you might have a business rule stating that no customer can have an outstanding balance of more than $10,000. There are various ways to implement business rules, most notably by assigning validation rules at the field or table levels. (Chapter 5, "Building Tables," discusses validation rules.)
Sometimes putting a business rule into effect can be assisted by constructing a validation table. For example, say that a business rule limits exports to a defined set of eight countries. You could create a validation table that includes values with just these eight countries. When you need to enter a country in the data table, your choices would be restricted to these eight values.
The values in a validation table usually change infrequently. Often the table has only two fields, a primary key that identifies each record and the value (see Table 3.33). You place the primary key of the validation table into the data table as a foreign key (see Table 3.34). The two tables have a one-to-many relationship: The CountryID field in the ShipCountry table can appear many times, but it can appear only once in the Countries table.