Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2016
    Posts
    6

    Help design a membership

    I am build a small app of club manager, and i have some trouble
    in design the membership part.

    So i would have a membership table which contain: id_pk, name, visitPerrMonth, numOfVisit
    And these are the action that i would like to do:

    Add time to an active membership,
    Put on hold,
    Put on hold in feature date
    Log every action on a membership

    At first i create one table which contain all these data and perform as log as well,
    but that lead to a lot of duplicated data.

    So i separate things and this is what i have now: Click image for larger version. 

Name:	1.jpg 
Views:	13 
Size:	94.7 KB 
ID:	17090

    Is it better approach?

    Thanx

    p.s I used sqlite

  2. #2
    Join Date
    May 2016
    Posts
    89
    Hi Tom64000

    First question, Could you precise which data you need to follow over time?
    For example, do you need to store historic of status?

  3. #3
    Join Date
    Aug 2016
    Posts
    6
    Yes, i would like to know over time the history status as well as every action that perform on a membership.

  4. #4
    Join Date
    May 2016
    Posts
    89
    What happens when a member stop his registry and active again? Does he keep the same subscription number or do you generate a brand new?

  5. #5
    Join Date
    Aug 2016
    Posts
    6
    Yes, he would keep the same subscription
    number

  6. #6
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    I propose you this logical database model with entities as follows :

    fee schedule:
    Price
    startDate
    endDate


    subscription:
    idregistry = subscription number
    first name
    last name

    fee?????
    idregistry
    idfee
    StartDate
    endDate
    price

    To finalize the LDM, could you explain me rules about fees and subscription.

    Is a subscription period standardized (one year for example) or a free period?
    Do you pay fees once a period subscription or many times for a subscription period?

  7. #7
    Join Date
    Aug 2016
    Posts
    6
    Sorry for the delay and thank you for you'r time.

    So to answer your question, the period came form the membership type,
    each membership as it's won period and therefor it's won price.

    If i understand your propose, you mean i would do something like that:

    A member buy a membership.

    1: I insert a row to a table called: "package_order" which contain: id(PK_autoincrement), membershipId(FK_membership), actionDate, memberId(FK_member), actualPrice, note

    2: I insert a row to a table name "package_status" which contain: id(PK_autoincrement), packageOrderId(FK_packageOrder), actionDate, actionType, startDate, endDate, status, note

    Now, every change that i made like: add time, put on hold, active or cancel...
    go the table "package_status" which can also be a log table to any action perform on a bought membership.

    Do you pay fees once a period subscription or many times for a subscription period?
    The member can pay whenever he wants, he can pay right away or a month later,
    he can even pay more then he need and have it save in his account for the next buying.,
    so therefore i though to make a table "account" which contain: id(PK_autoincrement), actionDate, memberId(FK_member), actionType(buy, pay, add money for later....), packageOrderId(FK_package_order), balance, note

    When buy i insert a negative number, when pay a positive..

    Is this approach make sense?

    Thank you again.

  8. #8
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    You mention a type which qualifies each member and set the price?


    1. Firstly, is it possible for a member to change his type?
    2. Secondly where is the type table and what are its fields?


    Could you confirm that a price depends on two criteria:

    1. Type
    2. Period


    What is the computation formula for the price, please?
    Last edited by informer; 09-09-16 at 04:56.

  9. #9
    Join Date
    Aug 2016
    Posts
    6
    What i meant by the column "type" in the table "package_status" is a type of action,

    Now, every change that i made like: add time, put on hold, active or cancel...
    go the table "package_status" which can also be a log table to any action perform on a bought membership.
    So this is my type, and this type is not qualifies each member but qualifies each action, (same apply in the account table as well)
    Secondly where is the type table and what are its fields?
    I probably would create a table of that type later.

    Firstly, is it possible for a member to change his type?
    If by that you mean change is membership then no.

    Could you confirm that a price depends on two criteria:
    Yes, i have a table called membership which contain: id(PK_autoincrement), name, numberOfMonth, numberOfMaxVisitPeerMonth, note

  10. #10
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    I think we need to change our approach for well modeling your database.

    Firstly I propose you to describe very precisely the subscription process that you want to set in place, ok?

  11. #11
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    I propose you this

    conceptual data model

    Click image for larger version. 

Name:	cdm_gym.jpg 
Views:	5 
Size:	103.3 KB 
ID:	17102
    And the logical data model associated
    Click image for larger version. 

Name:	ldm_gym.jpg 
Views:	7 
Size:	111.9 KB 
ID:	17103

  12. #12
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    I propose you this

    conceptual data model

    Click image for larger version. 

Name:	cdm_gym.jpg 
Views:	5 
Size:	103.3 KB 
ID:	17102
    And the logical data model associated
    Click image for larger version. 

Name:	ldm_gym.jpg 
Views:	7 
Size:	111.9 KB 
ID:	17103

    A member is active if there is a dateEnd inferior to the current date. But of course you can add a flag on member entity which is updated by coding
    Last edited by informer; 09-11-16 at 07:52.

  13. #13
    Join Date
    May 2016
    Posts
    89
    Hi tom6400

    I propose you these Conceptual & logical data model below:

    CDM
    Click image for larger version. 

Name:	cdm_gym.jpg 
Views:	4 
Size:	111.4 KB 
ID:	17104

    LDM

    Click image for larger version. 

Name:	ldm_gym.jpg 
Views:	11 
Size:	158.8 KB 
ID:	17105

  14. #14
    Join Date
    Aug 2016
    Posts
    6
    Ok, this is very helpful.
    thank you very much for your time and your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •