Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

Physical implementation of subtyping in a database is a complex issue. Unless you have a situation where it offers compelling advantages (see below for one or two examples) it adds complexity into implementation while providing relatively little value.

Having done this with really complex subtyping (applicaitons and sentences on a court case management system, disparate combined-risk commercial insurance contract structures) I guess I have some observations on this. Some significant corner cases are:

  • If the total number of database fields across the subtypes is relatively low (say: less than 100) or there is significant commonality between subtypes then splitting the subtypes out into separate physical tables is probably of little value. It will add significant overhead to reporting queries and searches. In most cases it's best to have a single table and manage your subtyping within the application. (Probably the closest to your problem)

  • If your subtyping is very disjoint, and different subtypes have type-dependent data structures hanging off them (i.e. child tables or more complex structures), then subtype tables make sense. In this case, each subtype probably has relatively little commonality within the application (i.e. there is probably a whole subsystem within the application dedicated to that subtype). Most reporting and querying will probably occur within a given sub-type, with cross-type queries mainly being restricted to a handful of common fields. (Court case management system)

  • If you have a large number of subtypes with disparate attributes and/or a requirement to make this configurable then a generic structure and supplementary metadata may be more appropriate. See this SO posting for a rundown on some possible approaches. (Insurance policy administration system)

  • If you have a very large number of fields with little commonality across your sub-types and little requirement to query across sub-type tables (i.e. nothing much in the way of multi-way outer joins against your sub-type tables) then sub-type tables may help to manage the column sprawl. (Pathologically complex version of your problem)

  • Some O/R mappers may only support a particular approach to managing sub-classes.

In most cases physical sub-type tables in a DB schema are a bit of a solution in search of a problem, as they potentially have undesirable side-effects.

In your case, I assume you have a relatively modest number of sub-types and a manageable number of attributes. Your diagram and question don't indicate any intention to hang child tables off the records. I would suggest that you consider going with the first option suggested above and maintaining one table and manage the sub-typing within your application.


Consider first developing a sound logical data model using the rules of data modeling classification hierarchy found in Enterprise Model Patterns, a book by David Hay. When creating a classification hierarchy, each occurrence (row) must be of one and only one sub-type. This means the sub types are mutually exclusive. The classification must be based on a single, fundamental, unchanging characteristic. Using this basic rule will provide a lot of clarity to your model. In the model you have, the single characteristic to classify on is purpose of the device - a phone, a network switch, a computer, a router, etc. Each device must be of one, and only one, of these types. So for example location would not be a sub type. Attributes such as IP address belong to the super type.

I think you will find that the number of device types will be large enough to warrant a EAV pattern as mentioned in another answer. The David Hay book I reference covers this pattern very effectively. However, if the number of sub-types are few you can a rule of thumb for deciding to implement only a super type table with many nullable columns, only sub type tables with duplicated columns, or both. If each sub-type varies greatly in its attributes and has no relationships at the super type level, you might go with only sub-type tables. If the opposite is true, you might go with only super-type tables. If there is a mix, then implement both.

Note finally you can always implement an EAV pattern as a base table schema and then create a view abstraction layer that presents the data to the application as super and sub type tables. This gives you flexibility at the storage layer but understand-ability at the application view layer.


A product is not inventory. Inventory and products are distinct.

A product is really a specification of a product, not a physical thing.

The physical thing is an Asset that the company owns (or stores). You can have assets that you track by serial number (discrete assets) or assets that you track only by quantity (inventory assets).

I would look at Silverston's Data Model Resource Book Vol 1. He has a good schema for proudcts, features, pricing, inventory. It will save you a lot of time.