I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club approve his request. If the number of members in club are less than three then all the existing members (whatever 2, 1 ) need to approve his request. My table looks something like this:
I am struggling to model this approval process in my database? Could anyone help me here? Sports club can have any number of users.
Best Answer
Add a self-reference to User. Call this reference "approved by". I'd suggest implementing this as a separate table but if you're absolutely sure you'll only ever need three approvers it can be colums in the user table.
As a current member approves an application count the number of approvals, compare it to the number of required approvals, and the number of current members and proceed accordingly. Remember to account for the case where the current applicant is the first to apply to a club.
I think it's an error to make a user exclusive to one club. Wouldn't it be good to know that the same person plays polo, frisbee, netball and Jai-Alai? It would be better to have an intersection table called Membership.