Current

The current, and considered old, database diagram. This is a direct database export using auto lay outing. Considering this is only here for historical purposes not much effort was put into this.

Due too automated generation the links between tables might be incorrect.

Stripped new version - auto generated

Old database stripped from some tables which should no longer be used (or are removed because they will be added again in the future (in their own microservice)

Stripped new version - edited

Some thoughts/proposals on this:

• Rename users to members
• There is too much overlap in fields between users and recruited users
• It is questionable what linking organization roles to bodies directly adds.
• The module concept is probably outdated considering the new architectural design in which the core is less monolithic
  • What is the difference between module pages and menu items?

I have not yet taken a detailed look at the fields inside the tables.

Proposal

By Derk Snijders

Notes:

• Styled tables are edited
• Renamed user → member, antenna → body
• Added Tables:
  • University
  • Address
  • body_roles
• Removed internal_email on member
  • Add later on through separate microservice, confusing at this moment
• Removed is_superadmin on member
• Removed other on member
• Not sure if suspended stuff should be directly in member
  • Maybe separate member_state table?
• Changed all the address fields to a reference to an address object
• Make new table study?
  • • Allows users to have multiple studies, as well as linking study types and fields as well as providing ability to set its state (in progress, completed, canceled, etc.)
• Removed recruited_users
  • I still do not really understand what this is supposed to do, but the current table has too much overlap with users.
• Suggestion
  
  • Add a deleted_at field to everything, this allows is to store past relations (and provide for example an AEGEE history for members)
• Added body_roles to allow many to many relation between body (types) and organization_roles
• Removed is_internal and is_admin from organization roles, did not really make sense.
• Currently having multiple roles in a body requires you to be multiple members of the body (ie. multiple body_memberships between the 2 objects in order to have multiple organization_roles)

Proposal version 2

By Derk Snijders

Based on feedback from Nico

Changes:

• Added studies table
  • Allowing multiple studies per user
• Added is_superadmin to member
• Added legacy_key to bodies
  • The currently used 3-letter code, very useful for legacy reasons.
• Naming consistency
• Reordered fields

Thoughts:

• Universities could even be abstracted into bodies, when we add something like a body_meta table.
  
  • Probably too abstract.

Proposal version 3

By Derk Snijders and Nico

The role part of the diagram (the white tables) were left untouched and are scheduled for a rework later on.

Summing up ideas:

Having members as the central, main, object and has the following:

• Auths
• Roles
• Studies
• Bodies

Auths

This is the object containing login (session) information.

Roles

Links the member to having certain permissional roles and permissions. To be replaced in the future.

Studies

A member can be linked to multiple studies. For each study the field and type can be stored as well as indicating its status (in progress, done, etc.). The university is also attached to a study.

Bodies

The most interesting part. A member can have multiple memberships to a body attached to it. A body has a certain global type (ie. locals, commissions, etc.) and a body has a set of circles it supports. Circles are a very loose concept, it can be used to indicate interest or have more strict meaning such as being board of a local. It really is just a way of grouping members. There are global circles, common among multiple bodies, but a body can also have local only circles (local specific committees). Local circles can extend global circles, remaining connected to the global group, but being able to customize it a bit to the body's liking.

A member can be a member of multiple bodies, in each having a (independent) set of circles he is in which in turn can be connected on a global level.