Employeee Benefits Database

  • Can somebody tell what is the bridge class between Employee and Benefit? Also what is the Multiplicity between Bridge class and Employee ////// Multiplicity between Bridge class and Benefit.

    Look at the attachment.

  • Gosh, this seems like homework. You should at least try to answer the questions and explain why . If you're wrong, then people will help.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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?

  • Don't want to do your homework for you, so I'll ask a question instead.

    What's the conventional way of breaking up a many-to-many relationship?

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Following on from Jeff Moden and his suggestion of sophistication, you should also consider whether the benefit is non-contributory, contributory or a loan.

    If non-contributory is there a limit, is it a simple cash sum or a pay percentage, is there a penalty clause (first $50 on dental treatment).

    If contributory, is there a limit on employer contribution, does it match employee contribution up to that limit, is there a scale for employer contribution e.g. pensions being 1% to 15% of net or gross.

    Does the benefit affect National insurance contributions, if payable, how is this calculated.

    If the benefit is a loan that will need paying back, does that feed into the pay deductions table and should it be taxable or non-taxable.

    Yes it seems like homework but you have to think about the entities you are modelling.

    Good luck

    ...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply