I'm designing a brand new DB model for a new system. My goal is to have it be stored in SQL and mapped into objects by Entity Framework. I'm heavily using Table-per-Hierarchy pattern in order to support a complex object hierarchy and have it be inforced "properly" by the DB engine.
I have the following situation that I am not sure how to handle:
I have a set of entities called Resources. Each resource is a specific type and contains a number of specific attributes.
- Resource (abstract class, maps to Resources table)
- Resource table has a discriminator column and a ResourceId as a Primary key
- classes such as: ServerResource (concrete class, inherits from Resource, maps to UrlResources table that's 1-1 to Resource... Overall there are about dozehn other Resources-types (number is growing)
- each type of Resource has a set of unique properties that are specific to that resource)
I also have a number of entities called Checks. Each resource contains a number of Checks. ResourceId is a foreign key in Checks table. Checks are slightly more interesting. There are Check types that are common to some or all types of resources. And there are some Check types that are very specific to certain types of resources. To be more precise:
- Check (abstract class, maps to Checks table, CheckId is primary key)
- OutageCheck (is supported by most types of resources)
- SslCertExpirationCheck (is supported by only 1 type of resource)
- etc. There are approximately 3-4 types of checks per resource type. 1-2 of those are shared across most resources, while the rest are custom to the specific resource
So, my question is about how to map Checks both in the ERD as well as Entity Framework. The most straight-forward approach would be to create a few dozen tables, each with a specific combo of Resource type and Check type. IE: ServerOutageCheck, StorageOutageCheck, UrlOutageCheck, UrlSslExpiraitonCheck, ServerLowMemoryCheck, etc.
This seems a little hard to manage and maintain. I would love to be able to share the common Checks within a single table, specific to that common check (ie: OutageCheck) and only deviate on checks that are resource specific (ie: SslExpirationCheck). Is this possible? Or is O^2 the best I can do from table-management perspective?
Last thoughts. My database is very frequenty read from and very INfrequently written to. I will also be able to cache the heck out of the reads, if necessary.
I'm going to refer you to three tags, and advise you to pay particular attention to the info tab in each.
single-table-inheritance class-table-inheritance shared-primary-key
Single table inheritance is like what you have tagged table-per-hierarchy. The info tab discusses the joys and the woes associated with having lots of NULLS in a single simple table.
Class table inheritance is an alternative, one that puts attributes that pertain to some subclasses only in tables where the attribute has meaning.
Shared primary key is a design technique that lets you enforce the one-to-one nature of a subclass/superclass relationship, and also gives you a handy way of joining specialized data with generalized data.
Most of what I have to say would merely repeat what's already in the info tabs in the three tags. You may find the associated questions and answers relevant to your case as well.