Your application and its database certainly deal with things of significance to your organization that you care to computerize, automate some of their aspects, and to store data about them. Product and services are examples of these things—the fundamental aspects that any organization must take care of.

This article by Djoni Darmawikarta introduces three patterns for modeling: Type, Subtype, and Category. These three logical data modeling patterns are common structures applicable to any kind of organization.

Before I cover the three logical data modeling patterns, let's review briefly how we typically model a type. Let's say you're in a car business. You can model a car as a Car entity shown in the figure below; its sample data values are in following table (I just use six numbers for the VIN, instead of the 17 characters VIN standard, in the sample). VIN (Vehicle Identification Number), the car serial number, is the unique key of the Car entity. The other attributes (Brand, Model, Year, and Manufacturer's Suggested Retail Price) can be thought as the type of a specific car  with a unique VIN. So, the type is in the entity itself. Note that you can have more than one car—each with a unique VIN—that have the same type, such as the first three Honda Accord in the sample table. If you have many cars of the same type, or, you have many car types and they're dynamic (have changes:  new, update, delete; for example, the update on the MSRP), you can easily recognize that this model is then not suitable—type model is a better solution.

Type

The ER (Entity Relationship) diagram of the following figure shows Car Type and Car entities and their relationship. Car Type defines each type of your cars—a type is a definition of something. The Car is the individual car, each with a serial number (Vehicle Identifier Number) that has a specific type defined in the Car Type. You can think of a Car Type entity as a template used (instantiated) by an individual car. Now you can have as many car types as you need, and type changes don't affect the cars. Table two tables after the figure contain sample data values of the Car Type—Car data model.

Note that a car can belong to one car type only. On the other hand, a car type can be the type of many cars.

How do we deal with product that doesn't have an individual identifier? Can we apply the same data modeling structure to, for example, commercial books? You certainly have inventory; each Inventory is an instance of the Book Type. The following figure shows the Book Type—Book data model and its sample data values, respectively.

You can also apply the same data model to intangible thing, such as Service; an individual service may be identified by, for example, a contract number. The following figure  and the last table in the article show the Service Type—Service data model and its sample data values, respectively.

Subtype

What if you have cars that have different sets of attributes, meaning different types? You can model the different types as subtypes. The following figure shows two subtypes of the Car Type entity: Passenger Car Type and Truck Type. The Car supertype has the common attributes of its subtypes while each of the subtypes has its different attributes.

Category

While Type is a definition of something, Category is a way to categorize something. While Service can be of only one type, it can be of more than one category—its relationship to Category entity is many-to-many. An example of category for Service is shown in the following figure and its sample data values in the table after it.

Note that you need to resolve the many-to-many relationship at implementation time

Summary

Type, Subtype, and Category are similar patterns for data modeling. This article introduces these three patterns and shows their differences.

One or more of them exist in most data model. If your initial data model doesn't have any one of them then you should re-inspect the data model.

About the Author

Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.