Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    Lightbulb Unanswered: Generalisation schema Help(GIF Schema included)

    I have a CASE(law firm type) table which uses a "case number" as a PK.
    Each case can have zero-many "case plans" of three different types of
    case plans, namely: (civil, tax, criminal).

    I have created 3 different tables for each respective "case plan" type as their
    details differ quite significantly. I have created an Audit table which holds
    information about updates and inserts made to these tables.

    My problem is referencing a specific "case plan" in the Audit table
    as the CasePlanID in the Tax,Criminal,Civil tables could be the same, and
    the only uniqueness to those tables is the table name (ie: Tax,Criminal,Civil).

    I have thought of placing a "CONSTANT" column in each table with the type of the
    Case plan as the value, but this introduces a lot of redundancy.
    I have also thought of having an audit table for each respective case plan table
    but this doesnt seem like good practice.

    Perhaps a Generalisation class between Case and caseplans would help.

    I also have to report across the case plans too.

    Any help would be much appreciated ;-)
    Attached Thumbnails Attached Thumbnails Schema.gif  

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What's interesting is that you have modelled them like they're inherited (as in OOP). What is the difference between the case plan types? Different columns? Are you likely to add another case plan type at some point in time?

    Edit : forgive me i'm being a goon. You clearly state that their details differ quite significantly. What are those details? Can they be normalised?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It would seem like your case plan type can be seperated into a seperate table as your case will ALWAYS have a plan type. So you can normalise those into another table:
    Code:
    CREATE TABLE CasePlanTypes(
       PlanTypeID INT NOT NULL,
       PlanName VARCHAR(255)
    )

  4. #4
    Join Date
    Mar 2006
    Posts
    36

    Types

    There will only be 3 case plan types and no more.
    The types differ quite a lot. there ARE about 10 columns of similarity between all 3, however, Civil and Criminal have about 20 additional columns and between civil and criminal, about 12 of those 20 are similar in data type, but semantically different. Example:
    Tax: {Gross_amount_taxed_for_quarter, Net_Amount_taxed_for_Quarter}
    Civil:{Gross_Amount_Reclaimed_for_quarter,Net_amou nt_reclaimed_for_quarter}

Posting Permissions

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