Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlguy_2020
Starting Member
1 Post |
 Posted - 2011-06-03 : 18:01:26
|
| 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? |
|
|
|
|
|
datamodel design patterns / your design opinion