Table Types

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:

• Data tables are the primary tables where most of your data is stored.

• Join tables, or linking tables, create a connection between two tables in a many-to-many relationship.

• Subset tables are data tables that contain fields that describe a specific subject and, thus, are better suited to having their own tables.

• Validation tables, or lookup tables, are used to validate data entered into other tables.

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.

Table 3.30. The Main Table of Music CDs

DiscID	Album Name	Genre	Date Bought	Retailer	Price
1	Moronic	Rock	9/20/2004	Amazonian	$12.99
2	Dino Sings Ella	Swing	10/10/2004	Music Wearhouse	$14.99
3	My Sweet Jean	Country	10/20/2004	Towel Records	$18.99
4	Britney's Best	Pop	10/22/2004	Towel Records	$16.99
5	South Atlantic	Broadway	10/26/2004	Amazonian	$14.99
6	Before Midnight	Jazz	10/29/2004	Protozoa Records	$16.29
7	Rubber Sole	Rock	10/30/2004	Allenby's	$22.49
8	Katz	Broadway	11/17/2004	Martin's	$18.49
9	Automatic People	Rock	11/9/2004	Albright's	$17.49
10	My Fair Lacy	Broadway	11/20/2004	Music Wearhouse	$14.59

Table 3.31. A Subset Table of Popular-Music CDs

DiscID	Artist	Best Of?	<<other fields>>
1	The Moronics	No
2	Dean Martine	No
3	Nashville Band	Yes
4	Britney Spores	Yes
6	Dexter Gorton	No
7	The Beetles	No
9	R.E.N.	No

Table 3.32. A Subset Table of Broadway Musical CDs

DiscID	Composer	Lyricist	Theater	<<other fields>>
5	Ricardo Roger	Oskar Hammerstone	Monaco
8	Andrew Lloyds	Tremor Lunn	Summer Garden
10	Fred Bottoms	Allan Learner	Mark Hollinger

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.

Validation Tables

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.

Table 3.33. A Validation Table with Two Fields

CountryID	Country
1	United States
2	Germany
3	Japan
4	South Africa
5	Egypt
6	Saudi Arabia
7	India
8	Peru

Table 3.34. The Primary Key of the Validation Table Is Placed in the Related Table as a Foreign Key

OrderID (PK)	ProductID (FK)	CustomerID (FK)	Order Date	CountryID (FK)	<<otherfields>>
10075	00123	002	10/16/2004	3
10075	00124	002	10/16/2004	2
10075	00125	002	10/16/2004	2
10076	00124	003	10/19/2004	4
10077	00126	002	10/21/2004	7
PK=Primary key; FK=Foreign key