Member Management System

There is the organization that does have branches and sub branches like lion's club. There is a head branch under which other are operated. Every branch has executive committee and general members. So, it is necessary to build the database to keep the record of these members. The executive committee will be changed every year. So, both current and past committee executive member should be maintained. Members are rewarded and punished according to their execution. These logs are also to be recorded. Every Members need to pay membership according to their designation. So, the record of that should also be done. Late membership fine also should be applied.

Requirements

The actual requirement of client are listed below.

• Store Branch and sub Branch Details
• Store Working Period Details
• Store Committe and General Members Details
• Store Designation, Roles And Access Details
• Store Member Awards and Alligations Details
• Store Membership and Penalty Details
• Store Payment Details
• Implement Login, Logout and Reset Password

Tools and Languages Utilized

Name	Type	Purpose	Icones
HTML	Language	Basic Structure
Css	Tool	Design and Decorating HTML
Bootstrap	Designing Tool	Design and Decorating HTML
JS	Scripting Tool	Used to Write Scripts
Node	Server Side Scripting Tool	Used to Write Server Side Script
Express	Server Side Scripting Framework	Used to Optimize Server Side Code
HandleBars	Templating Engine	Used to create Dynamic Client Side Templates
MySql	Database	Used for Storing and Quering Datas
PhpMyAdmin	Database Management Software	Helps to Organize MariaDb Database(MySql)
Npm	Package Manager	Package Server
MD	Documenting Tool	Document on Github

Setup

Setting up project is piece of cake. Follow the steps below.

1. Open Terminal
2. Clone the project using git clone https://github.com/itSubeDibesh/Member_Management_System command.
3. Run npm install command to fetch the dependencies.
4. Copy, Paste and Rename .env.example to .env

Setting up database

Your project wont work yet now all you need to do is create and setup database.

1. Download The Schema alwo available inside /Database/Schema/ directory.
2. Open PhpMyAdmin from Xampp Server
3. Create MySql Database with name membership
4. Import Membership Database to PhpMyAdmin

Creating the First User

1. Serve The Project
2. Login using Username as Dibesh and password as Dibesh@2019
3. Open the User and add new user.

Serving the Project

You need to have node installed and all the dependencies fetched inorder to run the project. Simply now execuate npm run watch on terminal with project directory pointed to launch the website.

Database Schema

A Basic database Schema Concept is shown below.

[Branch]-------> BranchId [PK]
         |-----> ParentId [FK] -> Branch Id Itself
         |-----> Name [Varchar(100)]
         |-----> Address [Varchar(100)]
         |-----> Contact [Varchar(20)]
         |-----> Status [Enum(Active,Inactive)]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[Designation]--> DesignationId [PK]
         |-----> Name [Varchar(100)]
         |-----> Membership Fee [Varchar(100)]
         |-----> Hirachy Value [int(8)]
         |-----> Remarks [Text]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[Role]---------> RoleID [PK]
         |-----> Name [Varchar(100)]
         |-----> Remarks [Text]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[Permission]---> PermissionId [PK]
         |-----> Name [Varchar(100)]
         |-----> Remarks [Text]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[RolePermission]
         |-----> RolePermissionId [PK]
         |-----> RoleID [FK]
         |-----> PermissionId [FK]
         |-----> Status [Enum(Active,Inactive)]

[User]-------> UserID [PK]
         |-----> RoleID [FK]
         |-----> UserName [Varchar(20)]
         |-----> Password [Text]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[Member]-------> MemberId [PK]
         |-----> UserId [FK]
         |-----> DesignationId [FK]
         |-----> Name [Varchar(100)]
         |-----> DOB [Date]
         |-----> Address [Varchar(100)]
         |-----> Profession [Varchar(100)]
         |-----> Gender [Enum(Male,Female,Others)]
         |-----> Contact [Varchar(20)]
         |-----> Status [Enum(Active,Inactive)]
         |-----> Joined Date [DateTime] -> Register New Membeship
         |-----> Membership Renew Status [TinyInt] -> Trigger when MemberShip Renews 
         |-----> Last Renewed Date [DateTime] -> Record Last Renewed Date
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[AlligationsAndRewards]-----> AlligationsAndRewardsID [PK]
         |-----> MemberId [FK]
         |-----> Type [Enum(Alligation,Reward)]
         |-----> Title [Text]
         |-----> Description [Text]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[PenaltyCriteria]-----> PenaltyCriteriaId [PK]
         |-----> Number of Exceeded Days [Int]
         |-----> Amount [Decimal(2)]

[Payments]-----> PaymentId [PK]
         |-----> MemberId [FK]
         |-----> Payment Title [enum(Membership Renew, Penalty, New Membership)]
         |-----> Amount [Decimal(2)]
         |-----> CreatedAt [TimeStamp]
         |-----> UpdatedAt [TimeStamp(ON Update)]

[Committe]----> ComitteId [PK]
        |-----> BranchId [FK]
        |-----> Starting Year [Date]
        |-----> Ending Year [Date]
        |-----> Name [varchar(50)]
        |-----> ComitteHead [Int] -> MemberId

[CommitteMember]
        |--------> CommitteMemberId [PK]
        |--------> CommitteId [FK]
        |--------> MemberId [FK]

Schema Diagram

Schema diagram generated by phpMyAdmin.

Schema diagram generated by dbdiagram using following code:

Enum "alligationsandrewards_Type_enum" {
  "Alligation"
  "Reward"
}

Enum "branch_Status_enum" {
  "Active"
  "Inactive"
}

Enum "member_Gender_enum" {
  "Male"
  "Female"
  "Others"
  "None"
}

Enum "member_Status_enum" {
  "Active"
  "Inactive"
}

Enum "payments_Payment_Title_enum" {
  "Membership_Renew"
  "Penalty"
  "New_Membership"
}

Enum "rolepermission_Status_enum" {
  "Active"
  "Inactive"
}

Table "alligationsandrewards" {
  "AlligationsAndRewardsID" int(11) [pk, not null]
  "MemberId" int(11) [not null, note: 'Relation with Member Table']
  "Type" alligationsandrewards_Type_enum [default: NULL]
  "Title" text [not null]
  "Description" text [default: NULL]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "branch" {
  "BranchId" int(11) [pk, not null, note: 'Store New Branch ID']
  "ParentId" int(11) [default: NULL]
  "Name" varchar(100) [not null]
  "Address" varchar(100) [default: NULL]
  "Contact" varchar(20) [default: NULL]
  "Status" branch_Status_enum [not null, default: "Inactive"]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "committe" {
  "ComitteId" int(11) [pk, not null]
  "BranchId" int(11) [not null, note: 'Relation With Branch Table on Branch Id']
  "Starting_Year" date [not null]
  "Ending_Year" date [not null]
  "Name" varchar(100) [not null]
  "ComitteHead" int(11) [default: NULL, note: 'Relation With Member ID on Member Table']
}

Table "committemember" {
  "ComitteMemberId" int(11) [pk, not null]
  "CommitteId" int(11) [not null]
  "MemberId" int(11) [not null]
}

Table "designation" {
  "DesignationId" int(11) [pk, not null, note: 'Set Designation ']
  "Name" varchar(100) [not null]
  "Membership_Fee" double [not null]
  "Hierarchy_Value" int(11) [not null]
  "Remarks" text [default: NULL]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "member" {
  "MemberId" int(11) [pk, not null]
  "UserId" int(11) [default: NULL]
  "DesignationId" int(11) [not null]
  "Name" varchar(100) [not null]
  "DOB" date [not null]
  "Address" varchar(100) [not null]
  "Profession" varchar(100) [not null]
  "Gender" member_Gender_enum [not null, default: "None"]
  "Contact" varchar(20) [not null]
  "Status" member_Status_enum [not null, default: "Active"]
  "Joined_Date" date [default: NULL, note: 'Register New Membership']
  "Membership_Renew_Status" tinyint(4) [default: NULL]
  "Last_Renewed_Date" date [default: NULL]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "payments" {
  "PaymentId" int(11) [pk, not null]
  "MemberId" int(11) [not null, note: 'Relation With Member Table']
  "Payment_Title" payments_Payment_Title_enum [not null]
  "Amount" double [not null]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "penaltycriteria" {
  "PenaltyCriteriaId" int(11) [pk, not null]
  "Number_of_Exceeded_Days" int(11) [not null]
  "Amount" double [not null]
}

Table "permission" {
  "PermissionId" int(11) [pk, not null]
  "Name" varchar(100) [not null]
  "Remarks" text [default: NULL]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "role" {
  "RoleId" int(11) [pk, not null]
  "Name" varchar(100) [not null]
  "Remarks" text [default: NULL]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Table "rolepermission" {
  "RolePermissionId" int(11) [pk, not null]
  "RoleId" int(11) [default: NULL]
  "PermissionId" int(11) [default: NULL]
  "Status" rolepermission_Status_enum [not null, default: "Inactive"]
}

Table "user" {
  "UserId" int(11) [pk, not null]
  "RoleId" int(11) [not null, note: 'Relation Between Role and User']
  "UserName" varchar(20) [not null]
  "Password" text [not null]
  "CreatedAt" timestamp [not null, default: `current_timestamp()`]
  "UpdatedAt" timestamp [default: NULL]
}

Ref:"member"."MemberId" < "alligationsandrewards"."MemberId" [update: cascade, delete: cascade]

Ref:"branch"."BranchId" < "branch"."ParentId" [update: cascade, delete: cascade]

Ref:"branch"."BranchId" < "committe"."BranchId" [update: cascade, delete: cascade]

Ref:"member"."MemberId" < "committe"."ComitteHead" [update: cascade, delete: cascade]

Ref:"committe"."ComitteId" < "committemember"."CommitteId" [delete: cascade]

Ref:"member"."MemberId" < "committemember"."MemberId" [update: cascade, delete: cascade]

Ref:"designation"."DesignationId" < "member"."DesignationId"

Ref:"user"."UserId" < "member"."UserId" [update: cascade, delete: cascade]

Ref:"member"."MemberId" < "payments"."MemberId" [update: cascade, delete: cascade]

Ref:"permission"."PermissionId" < "rolepermission"."PermissionId" [update: cascade, delete: cascade]

Ref:"role"."RoleId" < "rolepermission"."RoleId" [update: cascade, delete: cascade]

Ref:"role"."RoleId" < "user"."RoleId" [update: cascade, delete: cascade]

ERD Generated by Work Bench

The figure below is generated by MySQL WorkBench 8.0. You can find workbench module file in /Database/Schema/Model/ directory .

Setting Json Logs

If you need to set your logs in .json extension then simply update your environment variable SET_JSON to true in .env file and run your application using npm start.

Note : Don't set SET_JSON to true if you are using npm run watch becasus it utilizes nodemon module which monitors all the json and js files which will restart the server itself time and again and crash the project.

Folder Structure

Folder Structure along with uses are visualized below.

 ________________
| Root Directory |
|________________|
 |
 |----> Config [Configurations utilized for HTTP work flow]
 |
 |----> Controller [Business logic to operate with HTTP request]
 |
 |----> Database [Core Database connection, querybox and logic]
 |      |----> Schema [Database Schema SQL File]
 |
 |----> Images [Documentation Image Directory]
 |
 |----> LOGS [Server Generated Logs]
 |      |----> ERROR [Server Generated ERROR Logs]
 |      |----> QUERY [Server Generated QUERY Logs]
 |      |----> REQUEST [Server Generated REQUEST Logs]
 |
 |----> Middleware [Validation Middleware]
 |
 |----> node_modules [Node module dependencies]
 |
 |----> Public [Public directory served to client]
 |      |---->Assets [Assets served to client]
 |      |       |----> Css [Css served to client]
 |      |       |----> Images [Images served to client]
 |      |       |----> Js [Js served to client]
 |      |       |----> Vendor [External Vendor Dependencies served to client]
 |      |----> Icons [Icons served to client]
 |
 |----> Routes [Routes Registered for HTTP Request]
 |
 |----> Views [Views and Templates that would be rendered on client]
 |      |----> Layouts [Layout Used for Views]
 |      |----> Partials [Partial view used to handle layout]
 |      |----> All the Other Directory for the specific views
 ________________
| Root Directory |
|________________|

The image below shows the implementation of these folder structure.

Work flow

Lets follow the clients approach to get the resources.

1. At First the routs gets request from the client which is stored on webRoutes.js file. Here Instance of Express Application is Required as dependency to handle multiple routes. This routs gets initialized on server.init.js file. All the routes registered on webRoutes.js acts as a milldeware for the individual requests registered.

2. Then the request is passed to specific controller registered to the route under Controller Directory. A specific file is defined under this directory for every routes and All the task related to that route is performed under that specific file.

3. Controller has a Config Dependency stored on Config directory. Here Dependency like SQL euery execuator, Reguest Logger and Middelware handler are requested so that all these dependencies could be extracted on individual controller file as per required.

4. All the Database queries are stored on Queries.json file under Database directory. These Queries are required once on Http.js file as dependency file so that we could use all the queries independently on any controller file. These queries are stored under queryBox variable on Http.js and used on every controller file to extract query as per required.

5. All the Queries are Execuated using QueryExe.js file under Database directory as well. The Query fetched from queryBox is passed on Exe function of QueryExe.js. Exe function is also set as dependency on Http.js so, we can perform any query on any controller as per requirement.

6. Every Execuated Query Returns 2 response. i.e Result or error Result are manupulated as per requirement where as errors are logged on Error.log file using nodefslogger module which is also set as dependency on Http.js.

These were the basic workflow how project works. All the Logs are maintained under [LOGS] logs directory which is generated when server needs to maintain log. And all the database backups generated automatically and manually are stored under [BACKUPS] directory.

Some Extra Features

There are some extra fatures which will only be visible on certain circumstances.

1. Permission Update, Delete and Add function is only available if you set HIDE_PERMISSION_ACTIONS = true flag to false on .env file. If this flag is true you will not be able to see permission Update, Delete and Add function.

Permission Flag Enabled

Result

Permission Flag Disabled

Result

1. Similarly if you Desire to log the data set in json fromat than simply SET_JSON = false to true and run using npm start.
2. If you want to log the data from log file to console as well then set LOG_TO_CONSOLE = false to true.
3. If you want to clear logs everytime server restarts or starts then set CLEAR_LOGS = false to true.
4. If you want change root log directory then set LOG_DIR = LOGS flag to any directory name you desire.
5. If you want to change pagination limit then simply change SELECT_LIMIT = 10 flag value to amount of data you want to fetch while using pagination.

Setting Email

You can set Mail Server easily with minimal configuration You can observe this block on your .env file if you followed Setup properly.

# ------------------------------
# Node Mailer Configuration Starts
# ------------------------------
    MAILER_HOST = smtp.mailtrap.io
    MAILER_PORT = 2525
    MAILER_USER = 553582324dac8f
    MAILER_PASSWORD = b77dfe9eca847f
    DBA_EMAILS = "dibeshrsubedi@gmail.com"
# Node Mailer Configuration Starts

So,

• MAILER_HOST Defines the host used for mail service.
• MAILER_PORT Defines the port used for mail service.
• MAILER_USER is the email address from which email is being sent.
• MAILER_PASSWORD is the password of email address.
• DBA_EMAILS will be used to send error mails to DBA. You can add multiple mails using "comma" sign after every valid email.

Database Backup

There are two ways of database backup.

1. Automatic
2. Manual

Automatic Backup

Backing up Database Automatically needs a littel onetime configuration an all th eprocess would be execuating at it's owne. You can observe this block on your .env file if you followed Setup properly.

# ------------------------------
#  MySql Backup and Restore Configuration Starts
# ------------------------------
    DUMP_DIRECTORY = BACKUPS
    COMPRESS_FILE = true
    BACKUP_SCHEDULE_HOUR = 16 
    BACKUP_SCHEDULE_MINUTE = 30
    BACKUP_SCHEDULE_SECONDS = 05
#  MySql Backup and Restore Configuration Ends

So,

• DUMP_DIRECTORY Defines the directory where the backup files should be stored.
• COMPRESS_FILE Lets you to compress the backup file as save the file with .sql.gz instade of .sql reducing the size of the backup
• BACKUP_SCHEDULE_HOUR Lets you to repeate the backup at defined time hour every day. Set the values between 0-23.
• BACKUP_SCHEDULE_MINUTE Lets you to repeate the backup at defined time minute every day. Set the values between 0-59.
• BACKUP_SCHEDULE_SECONDS Lets you to repeate the backup at defined time seconds every day. Set the values between 0-59.

Another Important Factor for automatic backup is mailing address. Administrators will receive emails when automatic server fails in backup. Read Setting Email section if you have any confusion about email configuartion.

Manual Backup

So to have manual backup access you must have Backup permission and you will be propmt with backup facility on your dashboard. Just click the Backup Database button and your database backup would be stored on the backup directory. The picture below shows the backup view in dashboard.

Setting Server Port

If you want to change the server port then simply open .env file and update the fillowing section.

# ------------------------------
#  Server Configuration Starts
# ------------------------------
    PORT = 9876
#  Server Configuration Ends

Update the PORT value as your desire if dont want to serve the project at 9876 port.