Database Reference
In-Depth Information
Supertype and Subtype Tables
You have probably guessed this: When there are a good number of shared
attributes and a good number of differences in the subtypes, you will
probably implement both the supertype and the subtype tables. A good ex-
ample is a subtype cluster that stores payment information for your cus-
tomers. Whether your customer pays with an electronic check, credit card,
gift certificate, or cash, you need to know a few things. For any payment,
you need to know who made it, the time the payment was received, the
amount, and the status of the payment. But each of these payment types
also requires you to know the details of the payment. For credit cards, you
need the card number, card type, security code, and expiration date. For
an electronic check, you need the bank account number, routing number,
check number, and maybe even a driver's license number. Gift cards are
simple; you need only the card number and the balance. As for cash, you
probably don't need to store any additional data.
This situation calls for implementing both the supertype and the sub-
type tables. A Payment table could contain all the high-level detail, and
individually credit card, gift card, and check tables would hold the infor-
mation pertinent to each payment type. We do not have a cash table, be-
cause we do not need to store any additional data on cash payments beyond
what we have in the Payment table.
When implementing a subtype cluster in this way, you also need to
store the subtype discrimination, usually a short code or a number that is
stored as a column in the supertype table to designate the appropriate sub-
type table. We recommend using a single character when possible, because
they are small and offer more meaning to a person than a number does. In
this example, you would store CC for credit card, G for a gift card, E for
electronic check, and C for cash. (Notice that we used CC for a credit card
to distinguish it from cash.) When querying a payment, you can join to the
appropriate payment type based on this discriminator.
If you need data only from either the supertype or the subtype, this
method offers two benefits: you need go to only one table, and you don't
retrieve extraneous data. However, the flip side is that you must determine
which subtype table you need to query and then join both tables if you
need data from both the supertype and a subtype table. Additionally, you
may find yourself needing information from the supertype and multiple
subtypes; this will add overhead to your queries because you must join
multiple tables.
Search WWH ::




Custom Search