Packages and Subtypes
Designing Database Tables Part 8
Forward: In this part of the series, we look at subtype (generalization) relationships in packages as you design your database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
In the last part of the series, we were looking at the purchasing package. There are four relationships in the package. We saw two in the last part. We shall look at one of the four, which is subtype or generalization in this part of the series.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Normalized tables from Subtypes
Open the following link in a new tab window. You should be toggling between this page and the newly opened page as you read (link below).
The lower part of the diagram shows a subtype relationship. It involves the Items, which are RawMaterials, Components and OfficeSupplies classes (tables). Recall: in order to have normalized tables from the many-to-many relationships between the Item table and the PurchaseOrder table we inserted a new table in between them. The item table is still valid as well as all the other tables in the Purchase package. We now have to work with the Item table and the three tables below it in order to have the normalized tables for the lower section.
Generalization or subtypes mean the same thing in database. Any item that is bought by the company is bought to be used for work in the company. Consider that as a business rule for the company. In other words, an item is a generic description of something that the motorcycle company purchases to use for work.
We know that every item has a price and a description (what it is). However, in this company every item bought falls in one of three groups. An item is a raw material, a component, or an office utensil (office supplies).
Any item bought is a raw material, a component or office supply. Each of these groups has properties (table columns) that do not belong to the other two groups, but have to be tracked (monitored). The groups are called subtypes. They are subtypes to the Item class.
Let us look at some properties that are particular to each group. The price and description properties are for all the three groups. So these should be in the Items table. The company needs to track the weight of raw materials. In the components and office supplies groups weight is not important for this company. The company needs to track the dimensions of the components, but dimensions are not important in the other two groups for this company. The company needs to track the discount offered to the company for each office supply, but discount is not important in this company for the other two groups. These statements are examples of business rules. There may be more than one property in one group that are not in the other groups.
With what I have said, you have the following tables:
Item(ItemID, Description, price)
RawMaterials(Weight, )
Components(Length, Width, Height)
OfficeSupplies(Discount, )
You have four tables above; the Item table and the three other tables. These tables are not linked by keys. To normalize these tables we have to bear in mind that the Item table does not refer to any concrete item. In fact all these item tables can be compressed into one Item table as follows:
Item(ItemID, Description, price, Weight, Length, Width, Height, Discount)
In such a table, there will be null values; for example, if the item is a raw material, in its row, there will be null values for the Length and Height cells. In this crude table each row in the table represents a concrete item.
A better way is to have the four tables and then place the ItemID in all the subtype tables, giving:
Item(ItemID, Description, price)
RawMaterials(ItemID, Weight, )
Components(ItemID, Length, Width, Height, )
OfficeSupplies(ItemID, Discount, )
These four tables are in at least the 3NF; that is fine. Our aim was to establish normalized tables from the subtypes, which we have in a package, and we have done so. There is a one-to-one relationship between Item table and the subtype tables. Any row in a subtype table has a corresponding row in the Item table for the items description and price. Any row in the Item table has a corresponding row in one of the subtype tables, not in the other two; in the subtype table, you have the specific properties.
For now do not worry about the other columns to complete the table; we shall see how to do that soon. Remember, packages are related through tables; we shall see such relations, later.
We have come to the end of this part of the series. Rendezvous in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT