• sasansamani (3/18/2015)


    Yes this was homework.....

    When tried to solve problem I determined the relationship between Employee and Benefits is many to many but I cant determine the bridge class. I mean what should I name this bridge class. I cant envision anything.

    However, now I think about it the relationship between these are one to many. Each instance of employee can have one or many benefits. AND Each instance of benefit can have one and only employee. Do you agree?

    There shouldn't be multiple instances of benefits. There should just be on instance of each benefit in a Benefit table. Obviously there should be only one instance of each Employee. The relationship between the two tables would then become a many-to-many relationship with constraints. Each employee could have zero to all benefits and each benefit could have zero to all employee in the relationship. That would (should) be represented by a separate "bridge table" (probably called Employee_Benefit... I typically use the underscore only to name such bridge tables) with an Employee identifier, a Benefit identifier, and a start and end date for each Employee/Benefit pair. The table would have to have a constraint that would allow only one instance of each benefit to be active for an given employee.

    A level of sophistication could be added to the Benefit table as start and end dates for the given benefit with a trigger on the table that would update the bridge table end dates when a benefit was end dated. The same would hold true for the Employee table. If an employee is "end dated", then all benefits except those must continue by law should have the same end date.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)