Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: datamodel design patterns / your design opinion

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sqlguy_2020 View Post
    So does this design follow a formal database design pattern?
    i dunno about formal, but this is a well-known structure, called supertype/subtype

    seems you've discovered/reinvented it on your own, nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    6
    Quote Originally Posted by r937 View Post
    i dunno about formal, but this is a well-known structure, called supertype/subtype

    seems you've discovered/reinvented it on your own, nice job

    r937 - Thanks for your help - I can tell you're a database specialist

    I just posted another question a few minutes ago: "FK constraint -vs- custom constraint for role reference?"

    Would you mind taking a look at this? I need to implement this before work on Monday and would really like an expert opinion on this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •