Simple membership database design approach

Question

I'm a bit unsure what is best way to tackle one requirement that I have on my project (Laravel API and Angular UI). So 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 as simple as possible, because this is a very simple admin app, where admin user will manually mark paid status for each member when they pay, so there is nothing special or advanced here. On UI it should look like this:

So basically every year members can pay membership for every month in that year.

Note: x means member payed 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 consider to use, I will chose one of them, based on suggestions

So basically the idea behind this is pretty simple, every time member pays, membership table will be updated with date and member id, and that way I will have all payments for every member.

Because of UI from first picture I have few problems that I'm not really sure how to approach.

In order to generate bootstrap table from first image I'm thinking something like, 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 told me that it might be a "good" idea to create simple table with member_id, year, and all 12 months as attributes and just store true/false for each month there. Something like this:

Also one thing that bothers me, is it good idea maybe to first get all members, and then get all data from membership table based on member ids, so for example if I have pagination of 20 members I will make db call like this:

1. Get page 0 with 20 members
2. select * from membership m where m.member_id in (list_of_ids)

Answer 1

You do not want to store membership information in columns. Either membership or another_membership are fine -- they store the values in rows.

Which to choose? That is really up to you. For a general application, I would go for something like:

• MemberShipId
• EffDate -- from date
• EndDate -- to date
• PaymentDate
• . . . other information

You are set on the memberships being for one month periods, so having either a single date (say the first of the month) or splitting out the columns into (year and month) are fine.

Answer 2

What @GordonLinoff writes is how I would structure the database.

The next thing you probably need is a way of pivoting your rows to columns; you probably end up with a query that produces output along the lines of:

UserName Month Member
---------------------
Tom      Jan18    null
Tom      Feb18      1
.....

This question shows you how to convert that into the rows your front-end cares about.