Database Reference
In-Depth Information
was ready to go, we need to make a little change for consistency's sake.
Adding another relationship between tbl_address and tbl_employee allows
us to store employee addresses in tbl_address. Later, when we look at busi-
ness rules, we will reconcile the fact that we now have two optional rela-
tionships in tbl_address.
Last, but not least, we have the subtype construct we modeled for pay-
ments. As we discussed in Chapter 3, you have three options when you
physically implement subtypes.
Implement the supertype and all the subtypes in a single table.
■
Implement the subtypes as tables, and add the supertype data to
each of these subtype tables.
■
Implement the supertype as a table and all the subtypes as addi-
tional tables.
■
Which option you choose is dependent largely on the specific data you
are working with. In the case of Mountain View Music we will implement
a single payment table that will contain all the data from each of the sub-
types. This decision is often driven by the number of attributes in each en-
tity. Because the only attributes common to all payments are the account
holder and the payment type, we don't want that to be in its own table.
Especially with our addition of an address table, that would leave a pay-
ments table with two columns.
We could implement each payment as its own table, but that would be
a relationship nightmare. It would leave us with three separate relation-
ships to order (depending on the payment type) and three relationships to
address (one for each type).
It is for these reasons that we decided to go with a single payment
table. This table will contain a relationship to address for the payment ad-
dress and all the detail from each of the subtypes. When you look at the
payments table in Figure 9.9, you will probably notice that many of the
columns are optional; that is, they allow NULL data. That could lead to
problems because we require certain pieces of information to, for example,
process a credit card. We will fix this problem a little later when we look at
implementing business rules.
With that, we have looked at all the tables that are related to order and
have created the foundation for the Orders submodel, as shown in Fig-
ure 9.10. These tables represent the bulk of the Mountain View Music
database and were also a bit trickier to derive from our logical model. We
Search WWH ::
Custom Search