trjhtr

I'm a bit unsure of the best way to tackle one requirement that I have on my project. I have one pretty straightforward task: I need to create database schema for members and their subscription status for months in selected year.

I need to keep this simple as possible, because this is very simple admin app, where admin will manually mark paid status for each member when they pay, so there is no need for anything special or advanced here. On UI it should look like this:

Every year, members can pay membership for every month in that year.

Note: x means member paid membership for that month.

From database standpoint I'm thinking and trying to decide about something like this:

Note: membership and another_membership are tables that I'm considering using; I will chose one of them, based on suggestions.

The idea behind this is pretty simple: every time a member pays, membership/another_membership table will be updated with date and member id, and in that way I will have all payments for every member.

Because of UI from first picture I have a few questions that I'm not really sure what is best approach.

In order to generate bootstrap table from first image, I'm thinking something like this: for example, if I want to get all memberships for year 2018 from certain selection I will do something like:

Select * from member m
 left join membership ms
 on m.id = ms.member_id
where date >= 01-01-2018
 and date <= 01-31-2018
 and m.selection_id = 1

But I'm still unsure what is best way to map members each month status. I'm thinking maybe to group all dates from membership table and then somehow to generate this table.

Anyone have any suggestions on this? Should I use membership table or another_membership table? Should I do mapping (members and months statuses) on UI or API side? Any other suggestion, someone gave me advice that maybe it would be "good" idea to create table with member_id, year, and all 12 months as attributes and just store true/false for each month there? something like this: