CSSE Student Club Database Design

Intro

The purpose of the database is to serve the needs of the Monash CSSE Student Club in tracking members and Events.

The Tables

Members and member Info

Permissions on Online-system

Events

Table: members

Table of all the members stored in the system. This table stores most of their personal details.

Field Name
 Type
 Req
 Uniq
 Description
member_id
 ID
 Y
 Y
 Auto-generated Membership ID
first_name
 String (25)
 Y
 N
 First Name
last_name
 String (30)
 Y
 N
 Last Name
student_id
 String (15)
 N
 Y
 Monash Student ID
address
 String (150)
 N
 N
 Postal Address
email
 String (50)
 N
 N
 Email Address
phone_home
 String (20)
 N
 N
 Home Phone Number
phone_mobile
 String (20)
 N
 N
 Mobile Phone Number
degree
 degrees.degree_id
 N
 N
 Link to degrees table
member_type_id
 member_types.member_type_id
 Y
 N
 Link to Member Type

Table: member_types

Types of club members. Being a specific member type can give you extra priviledges on the system.

Field Name
 Type
 Req
 Uniq
 Description
member_type_id
 ID
 Y
 Y
 ID of member Type
name
 String (30)
 Y
 Y
 Name of Member Type
desc
 String (512)
 N
 N
 Description of Member Type

Table: positions

Table of positions in the club. Being in a position can give you extra privlidges on the system.
Field Name
 Type
 Req
 Uniq
 Description
member_id
 members.member_id
 Y
 N
 Which member has this position
position_id
 ID
 Y
 Y
 The ID of the position
desc
 String (512)
 N
 N
 Brief description of position
name
 String (50)
 Y
 Y
 Name of Position

Table: permissions

Field Name
 Type
 Req
 Uniq
 Description
permission_id
 ID
 Y
 Y
 Id for permission
name
 String (50)
 Y
 Y
 Name for permission
default
 Boolean
 Y
 N
 Default setting (if not otherwise explicitly stated in granted_permissions, this is used).

Table: granted_permissions

Field Name
 Type
 Req
 Uniq
 Description
member_id
 members.member_id
 Y
 N
 Member who has these privlidges
position_id
 positions.position_id
 N
 Position who has these privlidges
member_type_id
 member_types.member_type_id
 N
 Member type that has these privlidges
permission_id
 permissions.permission_id
 Y
 N
 The permission beieng granted
granted
 Boolean
 Y
 N
 If this permission is granted or revoked.

Table: events

This table is used to list all the events that the Club runs. Also coming under the 'events' banner is membership (i.e. club membership is a year long event).

Field Name
 Type
 Req
 Uniq
 Description
event_id
 ID
 Y
 Y
 Id for event
event_type_id
 event_types.event_type_id
 Y
 N
 The Type of Event (meeting, event)
name
 String (100)
 Y
 N
 Name of event
desc
 String (512)
 N
 N
 Description of event
start_datetime
 Date&Time
 Y
 N
 Start
end_datetime
 Date&Time
 Y
 N
 End
website
 String (255)
 N
 N
 Web site URL (could be generated, or set to something new and fancy)
contact_member_id
 members.member_id
 Y
 N
 Who to contact regarding event.
cost
 currency
 N
 N
 Base cost for members (can be left blank and specified in more detail on website)

Table: event_types

The event_type table is used to categorise events (such as Meetings, AGMs, Open Events, etc)
Field Name
 Type
 Req
 Uniq
 Description
event_type_id
 ID
 Y
 Y
 ID of event Type
type
 String (30)
 Y
 Y
 The type of event

Table: events_signup

This table is used to track which members have signed up to what event.
Field Name
 Type
 Req
 Uniq
 Description
event_id
 events.event_id
 Y
 N
 What event
member_id
 members_member_id
 Y
 N
 Which member
status_id
 events_signup_status.status_id
 N
 N
 Status of the Members signup

Table: events_signup_status

Used to show status of members signed up for events (e.g. interested, signed up, confirmed, paid). If the event_id field is blank, status can apply to any event.
Field Name
 Type
 Req
 Uniq
 Description
event_id
 events.event_id
 N
 N
 ID of event
status_id
 ID
 Y
 Y
 ID of status
status
 String (30)
 Y
 N
 The Status

What's Left?