Membership app: expired subscriptions [RESOLVED]

[Stevebenson223]

Hi all

This is as much a design problem as a technical problem.

I am building a simple membership app in Fabrik which is almost complete. But I have hit a problem in displaying members whose subscriptions have expired and have not been renewed. This is basically because of the design of my database, but I can't see an alternative design.

I have designed the database/app using three basic tables:
1) members: name, email, address, contact details, etc
2) membership type: membership plan, cost, expiry type (one year, never)
3) subscriptions: databasejoin to members table, databasejoin to membership type table
(there are other tables and fields, but these are the principle ones).

To me, this seems a logical and efficient design for the database. However, I need to show a list of members whose subscription has expired. I created this using subscriptions and it works, BUT it shows all expired subscriptions EVEN IF the member has now purchased a new subscription. Obviously, this is because it is looking at subscriptions and I need it to look at members. I feel I need to add a field to the members table to show a current subscription but I've not been able to find a way to do this.

I guess my questions are: is my database design basically correct and if so, is there a "standard" way to handle expired subs? There must be lots of people who have built similar apps?

Thanks to anyone who can point me in the right direction.

 

[troester]

I think you don't want
"to show a list of members whose subscription has expired" (= any of there (also old) subscriptions)
but
"to show a list of members who don't have any active subscription"
or
" whose last subscription has expired"

 

[Stevebenson223]

You are correct @troester
I want "to show a list of members who don't have any active subscription".

So I need a field in the member table to record an active subscription?
In the Subscriptions table I have a field for expiry date and a field for current/expired which displays "Expired" if today > expiry date.
Should I try to get this latter field into the members table? I tried something similar using the Upsert plugin but it didn't work - I got a "duplicate primary key" error.
Any further thoughts?

 

[Stevebenson223]

Finally, with the help of @troester and @PaulV888 (from a 2016 post), I have realised that I can do all this with pre-filters AND that you can type a whole load of stuff in the Value field to extend the query!
1. Pre-filter to show latest record for each member using "SELECT MAX(subscriptions.date_time) FROM subscriptions WHERE 1 GROUP BY member" in the Value field
2. Second pre-filter (not grouped) to show records where expiry date less than NOW().

I am so happy!
Thanks guys!