Sunday, February 19, 2012

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 ;-)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?|||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:

CREATE TABLE CasePlanTypes(
PlanTypeID INT NOT NULL,
PlanName VARCHAR(255)
)|||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}

No comments:

Post a Comment