Multi‐Institute Database Design Decisions - liferesearchapp/life-research-members-portal GitHub Wiki

Institute Wise Database Design Decisions and Explanation

As part of the effort to enhance the portal for the multiple institutes, significant changes have been made to the underlying database architecture to support multiple institutes. The primary goal of these changes was to ensure that certain elements of the database are shared across different institutes, while others remain unique to each institute. As such, for the shared elements a new table was created for each to manage the many-to-many relationship between the elements and the institutes. For unique elements, they gained an additional field holding the foreign key to the institute model - instituteId. Below is a more detailed explanation of these design decisions:

Shared and Unique Elements Across Institutes

Shared Elements:

  1. Members: Individuals associated with various institutes can now be managed in a more flexible manner. The relationship between members and institutes is handled through the memberInstitute model. This allows for members to be associated with multiple institutes, facilitating collaboration and access across institutional boundaries.
  2. Products: Similar to members, products (like research outputs, software, etc.) can now be associated with more than one institute using the productInstitute model. This promotes an integrated view of resources available across institutes.
  3. Partners: The organization model holds details such as names in English and French, optional scope and type, and descriptions. It manages relationships with institutes via the OrganizationInstitute model, enabling partners (for example City of Ottawa) to be associated with multiple institutes and participate in various partnerships and events.

Unique Elements:

  1. Grants: Managed uniquely within each institute to reflect specific funding received and managed by that institute.
  2. Supervisions: Specific supervisory relationships are maintained within each institute to accurately represent the mentorship and academic guidance provided.
  3. Events: Each institute can host its own events, which are not shared across the network to maintain the distinctiveness of each institute's engagement and activities.

Introduction of Superadmin and Institute Admins

Superadmin Role:

  • The introduction of the is_super_admin flag in the account model allows for a superadmin role, which is crucial for overarching administrative tasks like adding new institutes. This role has the ability to manage institute-wide settings and configurations, enhancing the governance of the portal.

Institute Admins:

  • The new instituteAdmin model replaces the previous is_admin column in the account model. This change allows for a more granular and institute-specific administration. Each institute can have one or more admins, and the relationships are managed through the instituteAdmin model linking accounts, members, and institutes.

Schema Updates

Institute Model:

  • Represents each institute with unique identifiers, descriptions in multiple languages, and relationships to its members, products, organizations, supervisions, events, and grants.
Field Type Description
id Int Unique identifier for each institute, autoincremented.
name String Unique name of the institute.
urlIdentifier String Unique URL identifier for each institute.
description_en String? English description of the institute, optional.
description_fr String? French description of the institute, optional.
events event[] List of events associated with the institute.
grants grant[] List of grants associated with the institute.
instituteAdmin instituteAdmin[] List of admin users associated with the institute.
members memberInstitute[] List of members associated with the institute.
organizations OrganizationInstitute[] List of organizations linked to the institute.
products productInstitute[] List of products associated with the institute.
supervisions supervision[] List of supervisions specific to the institute.
is_active Boolean Active/Inactive flag, default active (true).

MemberInstitute Model:

  • Manages the many-to-many relationships between members and institutes, allowing members to be affiliated with multiple institutes.
Field Type Description
memberId Int ID of the member.
instituteId Int ID of the institute.
institute institute Relation to the institute (foreign key reference).
member member Relation to the member (foreign key reference).
@@id [memberId, instituteId] Composite key of memberId and instituteId.

image

ProductInstitute Model:

  • Similar to the memberInstitute model, this manages the relationships between products and institutes.
Field Type Description
productId Int ID of the product.
instituteId Int ID of the institute.
institute institute Relation to the institute (foreign key reference).
product product Relation to the product (foreign key reference).
@@id [productId, instituteId] Composite key of productId and instituteId.

image

OrganizationInstitute Model:

  • Handles the affiliations between organizations and institutes, allowing for shared administrative and organizational structures across institutes.
Field Type Description
organizationId Int ID of the organization.
instituteId Int ID of the institute.
institute institute Relation to the institute (foreign key reference).
organization organization Relation to the organization (foreign key reference).
@@id [organizationId, instituteId] Composite key of organizationId and instituteId.

image

The InstituteAdmin model can be viewed in Multi-Institute-Admin-Support.