Type, Subtype, and Category Patterns in Logical Data Modeling

by Djoni Darmawikarta | December 2008 | Architecture & Analysis

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, Subtype, and Category Patterns in Logical Data Modeling

 

VIN

Brand

Model

Year

MSRP

123987

Honda

Accord

2007

20,000

456321

Honda

Accord

2007

20,000

555666

Honda

Accord

2007

20,000

678345

Toyota

Corolla

2008

21,000

 

...

 

...

 

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.

Type, Subtype, and Category Patterns in Logical Data Modeling

 

Car Type Key

Brand

Model

Year

MSRP

1

Honda

Accord

2007

20,000

2

Toyota

Corolla

2008

21,000

...

...

...

...

...

 

 

 

VIN

Car Type Key

Owner

123987

1

Djoni Darmawikarta

456321

1

Kevin Peter

555666

1

Rao Ganipineni

678345

2

Sherman Chang

...

...

...

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.

Type, Subtype, and Category Patterns in Logical Data Modeling

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.

Type, Subtype, and Category Patterns in Logical Data Modeling

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.

Type, Subtype, and Category Patterns in Logical Data Modeling

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

Type, Subtype, and Category Patterns in Logical Data Modeling

 

Service Category Key

Service Category

1

Bundled

2

Outsourced

3

Onsite

4

Software

5

Hardware

...

...

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.

Books From Packt

Professional Plone Development
Professional Plone Development

Microsoft Visual C++ Windows Applications by Example
Microsoft Visual C++ Windows Applications by Example

ASP.NET Data Presentation Controls Essentials
ASP.NET Data Presentation Controls Essentials

OpenCms 7 Development
OpenCms 7 Development

Entity Framework Tutorial
Entity Framework Tutorial

DotNetNuke Skinning Tutorial
DotNetNuke Skinning Tutorial

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

 


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
6
E
5
k
d
j
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software