Where Objects Work Better Than Relations
There are some database environments—especially those involving a great deal of inheritance—in which object-orientation is easier to implement than a relational design. To see why this is so, let's look at a hobby environment that just happens to be one of the best examples of the situation in question that I've ever encountered.
The database catalogs Yu-Gi-Oh cards (one of those animé related trading card games). The collector for whom this database and its application were developed has thousands of cards, some of which are duplicates. They are stored in three binders. Within each binder there may be several sections; the pages are numbered within each section.
There are three major types of cards: monsters, spells, and traps. The monster card in
Figure 18-12 is fairly typical. Notice that it has a title, an “attribute” at the top right, a “level” (count the circles below the name), an “edition” (first, limited, or other), a set designation, a type (and optionally two subtypes), a description, attack points, and defense points. At the bottom left, there may be a code number, which is missing from some of the early cards.
A card with the same name may appear in many sets and the same set may have more than one card of the same name. What distinguishes them is their “rarity,” determined by how the title is printed (black or white for common cards and silver or gold for rare cards) and how the image is printed (standard color printing or holofoil printing). There are a variety of combinations of printing to generate common, rare, super rare, ultra rare, and ultimate rare cards.
Note: If you want an interesting challenge before you see the relational design for this database, try to figure out the primary key for a card!
Most cards can be used for game play, but some have been banned from specific types of games. Others have caveats (“rulings”) attached to them by the game's governing board that affect how the card can be used in a game.
Spell cards, which as you might expect can be used in the game to cast spells, share a number of attributes with the monster card, but don't have things such as type and subtypes. The third type of card, a trap, also shares some attributes with monsters, but is missing others and has a property that is unique to this type of card. Spells also have properties, but the list of possible properties differs between spells and traps.
You can find an entity-relationship diagram for the card database in
Figure 18-13. As you might have guessed, there is an entity for the card, which has three subclasses, one for each specific type of card. There are also many holdings for each card.
To design the relational database, we create one relation for each entity, including the superclass (in this example, Card) and its subclasses (Monster card, Trap card, and Spell card). With an object-oriented DBMS, we would create objects only from the subclasses; no object would ever be created from the superclass. The subclasses “inherit” the attributes of their parent. For the relational database, we have to do that manually, using some type of primary key-foreign key relationship to connect the subclass tables to the parent table. Differences in where cards of a given name appear and how they are printed are handled by Holdings. Therefore, the design of the card database looks like this:
Card (InternalCardNUMB, Attribute, Banned?, CardDescription?, CardImage, CardName, CardNumber, CardType, Count, Limit, Ruling)
Monster card (InternalCardNumb, ATK, DEF, Level, MonsterSubtype1, MonsterSubtype2, MonsterType) Trap card (InternalCardNumb, TrapType)
Spell card (InternalCardNumb, SpellType)
Holdings (InternalCardNumb, Code, Edition, Holofoil?, NamePrint, NumberOwned, Binder, Page, Section, Slot)
Why have both the trap and spell card relations if they have exactly the same attributes? At the current time they could certainly be maintained in one relation. However, there are several reasons to keep them separate. First, there is no way to guarantee that they will always have the same attributes. If they are separated from the start, it will be easier to add attributes to one or the other if needed at some later date.
Second, the major reason this type of design doesn't perform as well as it might is because the details about a card always need to be looked up in another relation, joining on the internal card number. If we keep spell and trap data separate, the relations will remain smaller and the joins will perform better.
Note: Here's the answer to the primary key challenge: A Holding actually represents one or more physical cards in the inventory. It has a name (represented by the internal card number) and a set designation. When cards of the same name are printed in dif- ferent ways in the same set, they have different set designations. Therefore, the concatenation of the internal card number and the set designation uniquely identifies a card (although not a physical card in the inventory, given that there may be duplicate cards in the inventory). The only other alternative is to assign unique in- ventory numbers to each physical card and to use them. For some collectors, this may make sense, given that they would want to track the condition of each and every card.
There is an alternative design for this type of database: Rather than use one relation for each entity, create only a single relation in which many attributes may be null, depending on the type of card. Such a relation might look like this:
Card (InternalCardNumb, Attribute, Banned?, CardDescription, CardImage, CardName, CardNumber, CardType, Count, Limit, Ruling, ATK, DEF, Level, MonsterSubtype1, MonsterSubtype2, MonsterType, TrapType, spellType)
The Card Type attribute indicates which of the type-specific attributes should have data. For example, if CardType contained “M” for Monster, you would expect to find data in the ATK, DEF, and level attributes but not the spell type or trap type. The supposed benefit of this design is that you avoid the joins to combine the separate relations of the earlier design. However, when a DBMS retrieves a row from this relation, it pulls in the entire row, empty fields and all. Practically, in a performance sense, you haven't gained much and you're stuck with a design that can waste disk space.
Note: Personally, I prefer the multiple relation design because it's cleaner, wastes less space, and is much more flexible as the design of the relations needs to change over time.
A pure object-oriented design for the same database would include the five entity classes, although the Card class would be an abstract class. It would also include a class to aggregate all objects created from subclasses of the Card class, letting users handle all cards, regardless of type, as a single type of object. The nature of these data—the major need for inheritance— suggests that an object-oriented database may well perform better than a relational database.