Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    30

    Super/Sub Type Relationships : History Enabled ?

    Hi, I am implementing a super/sub type relationship for bank-accounts in my data model, and Im fairly familiar with the rules surrounding the modelling of these relationships, ( ie. 1-to-1, inherit PK, etc. ), but there is one boundary of these that I was looking to stretch, and I was wondering if anyone had any opinions on it, ie. is it breaking a golden rule, could you see it causing problems down the track, etc...

    Basically, I want to extend the 1-to-1 relationship between supertype and subtype, but including an effective date attribute in the subtype PK, therefore, the subtype actually keeps history on the elements that have changed directly within the subtype

    Refer to the attached diagram, but here also is some example data

    ACCOUNT
    =======
    1, Financial Services Account, Adams Account, Open
    2, Financial Services Account, Bobs Account, Open

    FINANCIAL SERVICES ACCOUNT
    1, 1-Jan-2004, [no date / high date], "ABC"
    2, 1-Jan-2004, 15-Jan-2004, "ABC"
    2, 16-Jan-2004, 4-Feb-2004, "DEF"
    2, 5-Feb-2004, [no date / high date], "GHI"

    Therefore, I there is still a 1-to-1 relationship for accounts, but not for the number of rows, but I have effectively kept history on subtype attributes... Is this too far deviated from modelling practises to be viable ?

    Also, does anyone have any opinions on whether a supertype can exist without a related subtype, I have read that it shouldnt, but what if it is not warranted ( ie. all information can be stored in supertype, therefore subtype would be empty anyway )...

    Thanks,
    Adam
    Attached Thumbnails Attached Thumbnails supersub_type.jpg  

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I've never done it, but I don't see a problem with it.

    Empty subtypes are ill advised.

  3. #3
    Join Date
    Mar 2004
    Posts
    30
    re: empty sub-types. therefore i would be best placed just having a supertype row without a subtype row / entity in this case, thereby breaking the suggestion that each instance of the supertype must appear in one subtype ?

    re: dated history tables. i guess if i made it clear that this was the way subtypes were being implemented, it shouldnt be a problem ? i thought it must violate an integral part of supertype/subtype relationships by not being truly 1:1 ( over time that is, any one point in time as specified by the dates it is effectively 1:1 )...

    thanks certus, does anyone else have an opinion on this please ?
    adam

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Quote Originally Posted by arnzie
    re: empty sub-types. therefore i would be best placed just having a supertype row without a subtype row / entity in this case, thereby breaking the suggestion that each instance of the supertype must appear in one subtype ?
    No, you must have a subtype row for every supertype row. What I meant was a subtype entity should contain more than a supertype key attribute.

  5. #5
    Join Date
    Mar 2004
    Posts
    30
    Fair enough, but the problem is that the source load for this particular (sub)type of account, contains only enough basic information to populate attributes in the supertype, therefore there is no leftover information to populate into a subtype... What would you suggest in this case, as its either an empty subtype ( why bother ), or a supertype with no subtype ( improper data modelling ).. ?

    Thanks for your help thus far certus, although my main problem / concern was the history-enabled subtypes, so if you or anyone else had any further opinions on that particular concern and the potential problems that could arise from it, that would be great... i guess if i did end up going with this approach, i should make it consistant ( for all super/sub types ), but in my case i cant, because i have some dimensions where there is no common natural key between the supertype and subtype, therefore would have to make these 1:1... Example :

    Events : natural key is specified wholly in subtype, therefore event is just populated afterwards as a reference that an event took place

    Rates : loaded as a cartesian dimension, meaning that all attributes are the key, depending on which subtype we are taking about... if i have 'interest rates', the natural key is all 'rate + interest rate' attributes, similar for any other rate subtype... Further more, does anyone have an opinion on this type of supertype/subtype setup, as loading it can be relatively complex considering the natural key is different depending on subtype you are loading.

    Regards,
    Adam

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I have to make a correction. The effective date is part of the supertype not the subtypes.
    Attached Thumbnails Attached Thumbnails 1supersub.JPG  

Posting Permissions

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