SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Employeee Benefits Database


Employeee Benefits Database

Author
Message
sasansamani
sasansamani
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 66
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.
Attachments
Employee Benefits Database (1).jpg (47 views, 30.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)

Group: General Forum Members
Points: 545135 Visits: 44658
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sasansamani
sasansamani
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 66
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?
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34307 Visits: 15524
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)SSC Guru (545K reputation)

Group: General Forum Members
Points: 545135 Visits: 44658
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7679 Visits: 5055
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

...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search