June 3, 2011 at 4:12 pm
My main background is in object-oriented application development.
In OO app dev we have the concept of "design patterns" which are formalized patterns that have been identified for solving common design challenges.
I wanted to find out if the concept of formal design patterns exists in the database world as well.
I think getting familiar with formal data model design patterns would give me more confidence as a data model developer and would make it easier for me to validate and justify design decisions.
I'll explain one scenario I've encountered on a previous project and I am encountering again on another project.
Example:
----------
A property management system has 5 different property types. Each property type shares some common fields but each property type also has a lot of unique fields.
For example, there might be a "Mall" property type and an "Apartment" property type. These property types will share some attributes like "Address" but the "Mall" property type will have unique attributes like "NumberOfStores" and the Apartment property type will have unique attributes like "HasSwimmingPool".
So:
----
My solution is to create a "Property" table that has all of the common attributes and then create a specialized property table for each property type like "PropertyMall" and "PropertyApartment" each with its own respective unique fields. In each specialized property table I put a PropertyId FK to the property table as a join to all of the common property attributes.
One scenario:
---------------
One scenario with this design is that a business user might want to get a quick list of all property types.
A solution for this would be to add a "PropertyType" table with values like "Mall" and "Apartment."
Each property type table can have a PropertyTypeID column that references the PropertyType table as a reference.
Having a unique PropertyTypeID in a lookup table also allows the datamodel to be extended easily to provide centralized associations of the property types with other things - for example, an AuditorPropertyType table that defines which property types an auditor has been licensed to audit.
In conclusion:
----------------
So in conclusion, this approach seems logical to me but I could see how some people might think it's weird to have a type table as well as specialized tables with type ids.
So does this design follow a formal database design pattern? Is this design similar to what you would suggest to solve this problem? Are there ways to improve this design or is this design pretty solid?
June 5, 2011 at 8:56 am
sqlguy,
I *think* you're on the money. I asked Louis Davidson (a SQL Server MVP/author) here in town about a question like that, and he says he deals with subclassing in his tables about the way you described. If you have a complex object that has many optional features, it's about the only way to model it without going out of your mind or having lots of "optional required" fields. (yes, that's a contradiction, but a very good indicator that you need a 1-to-1 join).
Hope this helps.
June 6, 2011 at 11:02 am
+1 from me too
I agree that you have chosen a good starting point.
Pros:
> You'll get type-safety out of your PropertyType-specific tables
Cons:
> You will avoid the trap of creating one very wide Property table with many sparsely populated columns.
> You will avoid the "property bag" model (columnar model) of storing attributes which has no implicit type-safety and is a pain is the arse when trying to return tabular results for binding to reports and data grids.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply