14

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

enter image description here

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

  • Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
  • Pro: No null fields.
  • Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

  • Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
  • Pro: Only have to use one table for CRUD operations.
  • Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

EDIT: A specific question I have regards the overlapping nature of the table "NetworkDevice". This table is meant to hold network information for any device with a hostname and/or IP address, whether it is a computer, switch, or router. Is the overlapping nature of this table something that could cause problems, or is it okay to implement it this way?

Thank you in advance for any input provided. Please ask if any additional information is needed.

Improve this question
1

4 Answers 4

Reset to default
18

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.

Improve this answer
3
  • Thank you for your detailed answer. I originally wanted to keep everything in one table, but certain fields for devices don't apply to others and I would end up with a bunch of null fields. For example, all inventory records would have fields for circuit type and service provider which are specific to routers. All records would also have a phone number field which doesn't make sense unless the device is a phone. Any suggestions on how to deal with this?
    – TheSecretSquad
    Commented Apr 15, 2012 at 20:51
  • 3
    @reallythecrash - The overhead for nullable fields is about one byte per field, so in terms of resource usage it's much less overhead than joining against subclass tables. Really the only drawback is that the table will look a bit messy with lots of nulls.
    – ConcernedOfTunbridgeWells
    Commented Apr 15, 2012 at 21:28
  • 3
    @reallythecrash - If you really want to (and your DBMS supports it - you haven't specified what you're using) you could set up check constraints based on the type discriminator that enforce null/not-null on the fields appropriate to the class.
    – ConcernedOfTunbridgeWells
    Commented Apr 15, 2012 at 21:31
3

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.

Improve this answer
4
  • Thanks for the info Todd. One of the questions I have is regarding the "Network Device" table. That table is intended to hold records for any device that has a hostname and an IP address. This means that switches, computers, and routers would all have their network related data stored in that table. From what I have been reading this is called an overlapping subtype where the subtype table holds related data for more than one type. Do you know if this is something that should be avoided, or if I am okay implementing this way?
    – TheSecretSquad
    Commented Apr 19, 2012 at 2:13
  • Todd, regarding your statement "create a view abstraction layer that presents the data to the application...". This sounds like a great idea. I thought of using views exactly as you described, but had some questions about it. I know it's okay to use views to query and display the data in my application, but is it common practice to use views for inserts and updates? I know there are some restrictions on how your queries must be structured (no order by clause, etc.) to insert/update using a view. If the query is structured correctly, is it advisable to use the view for inserts and updates?
    – TheSecretSquad
    Commented Apr 19, 2012 at 2:18
  • In my experience overlapping sub types confuse things at a logical level, which is why I was recommending going back to develop a full logical model first. You can use the LDM to clarify scope and understanding prior to dealing with storage. In the current model presented, there is some confusion of understanding between the fundamental nature of a thing - a device - and where that device lives in space. Clarify that in the LDM. Avoid the overlapping sub-type in the physical database as well unless you are using it to vertically partition columns in which case it is not typing at all.
    – Todd Everett
    Commented Apr 20, 2012 at 12:04
  • Regarding the abstraction layer, you can use an "instead of" trigger to make a view update-able. The restrictions you mention (no order by) are restrictions in the view SQL itself and not in its use. For insert/update there is no ordering anyway. Other options you have are to write a module to handle the details of the insert / update, or write a stored procedure to handle it. I see no problem using any of these methods as performance is acceptable. For singleton type writes it should be fine. Mass updates might be a problem.
    – Todd Everett
    Commented Apr 20, 2012 at 12:10
2

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.

Improve this answer
0
1

One of the questions I would ask is why are you tracking the various attributes of your inventory items? - Or, more specifically, what are you doing with this attribute information?

If you have a lot of reports or forms that make specific sense of particular attributes then you need to use the approach recommended by ConcernedOfTunbridgeWell. If on the other hand, these attributes are being recorded for the sake of listing them out, or possibly of comparing them with like attributes of similar devices, then you may actually have a (rare) good excuse to use EAV. I know that "EAV is pure evil" for many reasons, except for very rare cases when those reasons don't happen to matter to a specific application. Yours might be such an application.

Have a look at this answer regarding the design of a device inventory system and this answer regarding the design of a product catalog system to see how an EAV approach might simplify your application along with a discussion of exactly what the risks of EAV are and how to judge whether those risks may not apply to your specific application.

Improve this answer
1
  • Thank you for your input. I considered EAV, but thought I could achieve a good enough model without having to resort to the complexities involved with EAV.
    – TheSecretSquad
    Commented Apr 19, 2012 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.