Implementing Overlapping Supertype-Subtype Structures
Within the overlapping supertype-subtype structure, you can implement the data model in two ways: with a supertype table only or with a supertype table plus subtype tables.You wouldn't want to implement only subtype tables because of the danger of data redundancy every time you have a row of data that can fit into more than one subtype category (such as Person, which can fit under Prospect, Customer, or Vendor). Let's look at a brief example of each implementation and discuss why I've implemented them as I have.
Supertype table only: City University Class Scheduling System. City University has offered conventional classes for many years and recently began offering the same classes online. In the example model that Figure 3 shows, the supertype table would be Class, with Online and Onsite as subtypes. Course information, such as course code, long and short course titles, and description, are contained in an entity called Course, which is outside the supertype-subtype structure. Whenever a course is offered, it must have a time,location,instructor(s),and student(s),and there's an associated entry in Class.The online classes aren't offered on demand, so they must be scheduled the same way they would be if they were being held in a classroom. Both types of classes also need teachers, books and materials,and students.The subtypes represent different methods of delivery for a class, but neither subtype contains attributes that are specific to just that subtype. However, the supertype entity contains all the attributes that describe both subtypes and participates in all relationships that either subtype requires.The best implementation solution here is to implement just the supertype table.
Supertype table and subtype tables: County Library Titles Database. County Library has books, videos, and DVDs.The same title can appear in more than one format; print books that are reproduced as books on tape are also produced on DVD. These differing formats require different policies for retention, length of checkout, and storage. These titles have many attributes in common, but each has attributes and relationships that are specifically its own.For that reason,I'd implement a supertype table called Title plus a set of subtype tables that include Books,Videos, and DVDs.
Understanding Your Data
Supertypes and subtypes can help you understand your data requirements. If you take the time to carefully map out supertype and subtype relationships for your data, you'll create a more effective conceptual data model and a better-performing database.
End of Article
Prev. page
1
[2]
next page -->
