Institutions Management

• Introduction
• Tables
• Semesters Institutions
• Levels Institutions
• Institutions Availability
• Institutions Accreditation
• Direct Gifts
  • Institution Gifts
  • Program Gifts
• Discount Prices
• Levele Types

Introduction

The cornerstone of OTAS is its institutions component, which encompasses programs and maintains relationships with the cities and countries tables, thereby integrating geographical data into the educational offerings.

Note: For clarity and brevity in this document, we will reference OTAS database tables without the prefix spotlayerteam_institutionsprograms_. The tables will be referred to by their singular names, simplifying readability and understanding.

Based on the ERD (Entity-Relationship Diagram) provided, here are the key points describing the database structure for institutions within OTAS:

• Types: Represents various categories of institutions. One type can be associated with multiple institutions, indicating a one-to-many relationship.

• Institutions: The central entity representing educational bodies. Each institution has exactly one type, but there can be many institutions of the same type. Institutions also offer multiple programs.

• Programs: Denotes the courses or academic programs offered. Each program is linked to one institution, and an institution can have several programs.

• Cities: Indicates the geographical location of institutions. Each institution is located in one city, but a city can host multiple institutions.

• Countries: Each city is located in one country, meaning there's a one-to-many relationship from countries to cities.

• Regions: The highest geographical categorization in the diagram, where each country is part of one region. This means there's a one-to-many relationship from regions to countries.

In summary, an institution is categorized by a single type and offers various programs. It is situated in a city, which is located within a country, and each country is part of a single region.

Tables

In managing institutions within OTAS, the following tables play a pivotal role:

• Regions: Named spotlayerteam_institutionsprograms_regions, this table catalogs various regions such as Africa, the Middle East, Asia & Pacific, etc.

• Countries: Under the actual table name spotlayerteam_institutionsprograms_country, it lists countries along with their names in multiple languages (English, Arabic, Turkish, Persian, French, Russian, and Chinese). It includes country codes, flag URLs, phone codes, region IDs, and flags to enable country-specific listings for functions like program search, institution addition/editing, and institution accreditation.

• Cities: Known as spotlayerteam_institutionsprograms_city, this table records city names in the seven languages mentioned above, and it includes a country ID to establish a link to the corresponding country.

• Types: The table spotlayerteam_institutionsprograms_type details the types of educational institutions available, such as University, College, and Institution.

• Institutions: The central table in this schema, spotlayerteam_institutionsprograms_institution, holds comprehensive details about each institution, including city, type, location, and names in seven languages (English, Arabic, Turkish, etc.).

• Programs: This table houses the details of all programs offered by institutions, featuring numerous fields and attributes. The management of programs is a complex topic that will be addressed separately in a dedicated section on Programs Management.

Semesters Institutions

Every semester within the OTAS is flagged with an 'active' status to indicate whether it should be enabled or disabled within the system. Additionally, institutions have an 'available semesters' feature that allows administrators to view the semesters available in the country where the institution is located and select a subset of these semesters for use.

Note: Disabling a semester from the semesters page will result in that particular semester being hidden from the add/edit application interface for the associated institution.

The ERD for the relationship between institutions and semesters in OTAS reflects the logic for displaying available semesters to institutions based on their country and active status:

• Institutions: Entities representing educational bodies within OTAS.

• Countries: This entity categorizes all nations within the OTAS database and has a many-to-many relationship with semesters, meaning that multiple countries can have multiple semesters.

• Semesters: Lists the academic periods available within OTAS.

• Institution_Semester: This junction table links institutions and semesters. It facilitates a many-to-many relationship, allowing for a specific association between institutions and a selection of semesters.

The relationships suggest that:

• Each institution is associated with a single country but potentially multiple semesters through the institution_semester table.
• Countries are linked to semesters through a many-to-many relationship, which allows for multiple countries to be associated with multiple semesters.
• The institution_semester table specifies which semesters are available to which institutions. When entries exist in this table for an institution, it displays only the enabled semesters for that institution. If no entries exist for an institution in the institution_semester table, then all semesters enabled for the country associated with that institution are displayed.

This setup provides a flexible structure that permits customization of available semesters for each institution based on their location and individual preferences.

Levels Institutions

The ERD provided illustrates a many-to-many relationship between institutions and levels through an intermediary table, which is a common way to manage such relationships in database design:

• Institutions: This entity represents the various educational bodies within the OTAS system.

• Levels: This entity represents the different academic levels that can be offered by institutions, such as Bachelor's, Master's, PhD, etc.

• Institution_Level: This is the junction table that facilitates the many-to-many relationship. It contains references to both institutions and levels, indicating which levels are available at which institutions.

The relationships are as follows:

• An institution can offer multiple academic levels. For example, a university might offer Bachelor's, Master's, and Doctoral programs.
• An academic level can be offered by multiple institutions. For instance, the Bachelor's level could be available at many different universities.

The institution_level junction table would typically include a boolean field for each record to indicate whether a specific level is enabled or disabled at a given institution. This allows for granular control over the academic levels that an institution offers and can easily accommodate changes, such as adding new levels or discontinuing existing ones.

Institutions Availability

The ERD represents the structure of how institutions' availability is managed in relation to semesters, academic years, and levels within OTAS. This structure allows for the specification of when particular institutions are available for certain academic periods and levels. Here's a breakdown of the ERD:

• Institutions: These are the educational entities within OTAS.

• Levels: This refers to the academic levels an institution offers, such as Bachelor's, Master's, or Doctoral programs.

• Academic Years: This entity represents the academic cycles during which instruction is offered, like the 2021-2022 academic year.

• Semesters: This entity divides the academic years into segments, such as Fall, Spring, or Summer semesters.

• Institution Availability: This junction table connects institutions to levels, academic years, and semesters. It likely contains records that determine the specific combinations of when an institution is available for a certain level during a specific academic year and semester.

The relationships suggest:

• An institution has multiple levels at which it can offer programs.
• An institution's programs are available in multiple academic years and semesters.
• The availability of an institution for a certain level during a particular academic year and semester is defined in the institution_availability table.

This system allows OTAS to handle scenarios where, for example, an institution may only offer a Master's program during the Spring semester of the 2023-2024 academic year. By adjusting the records in the institution_availability table, the system can accommodate time-limited availability as well as ongoing offerings.

Institutions Accreditation

The country_institution table in OTAS is designed to handle the accreditation status of institutions based on various factors, such as country, level, and specialities. Here is a brief overview of its structure:

• country_id: Acts as a foreign key linking to the countries table, specifying which country the accreditation applies to.
• institution_id: Serves as a foreign key linking to the institutions table, identifying the institution being accredited.
• level_id: A deprecated column that is no longer in use, previously used to link to the educational levels for which the institution was accredited.
• specialities: A nullable JSON column that holds a JSON object where each key corresponds to a level ID, and the associated value is an array of all accredited specialities for that level in the particular country. If all specialities are accredited, the value may be null, indicating blanket accreditation.

This table allows the system to track and manage which institutions have been accredited for which specialities in various countries, a critical component of maintaining the integrity and credibility of the educational offerings within OTAS. The deprecation of the level_id column suggests that accreditation is now managed at the granularity of specialities rather than the broader educational level, allowing for more precise and flexible accreditation records.

Direct Gifts

The direct_gifts table catalogs incentives offered by institutions to students for enrollment, featuring:

• id: Primary key for gift identification.
• icon: A Font Awesome class for the gift's visual icon (e.g., fa fa-plane).
• name: Gift's name in English.
• description: Detailed description of the gift in English.
• name_ar, name_tr, name_fa, name_fr, name_ru, name_cn: Gift names in Arabic, Turkish, Persian, French, Russian, and Chinese, respectively.
• description_ar, description_tr, description_fa, description_fr, description_ru, description_cn: Descriptions in the aforementioned languages.

This setup promotes clarity and attractiveness of gifts across diverse student demographics through multilingual descriptions and visual icons.

Institution Gifts

The institution_gifts table facilitates a many-to-many relationship between institutions and the direct gifts they offer, allowing for the association of multiple gifts with multiple institutions. Here's a detailed look at the table's structure:

• id: Serves as the primary key, uniquely identifying each record with an auto-incrementing integer.
• institution_id: Acts as a foreign key linking to the institutions table, identifying which institution the gift is associated with.
• direct_gift_id: Functions as a foreign key linking to the direct_gifts table, specifying the gift that is being offered by the institution.

This structure enables the OTAS system to effectively manage and track the various gifts that institutions offer to attract students, ensuring that each gift is properly associated with the respective institutions. It provides a flexible way to add, update, or remove gift offerings as institutions change their promotional strategies.

Program Gifts

The program_gifts table establishes a many-to-many relationship between academic programs and the direct gifts associated with them, allowing for the specification of gifts that students may receive upon enrollment in particular programs. Here's an overview of its columns:

• id: Acts as the primary key for the table, with each entry uniquely identified by an auto-incrementing integer.
• program_id: Serves as a foreign key that links to the programs table, identifying the specific academic program associated with a gift.
• direct_gift_id: Functions as a foreign key connecting to the direct_gifts table, specifying the particular gift being offered in conjunction with the program.

This table structure enables the OTAS system to manage the allocation of gifts to various programs effectively, enhancing the appeal of certain academic offerings by associating them with attractive incentives. It provides the flexibility needed to tailor gift offerings to specific programs, thereby potentially increasing student interest and enrollment.

Discount Prices

The discount_prices table is designed to facilitate the management of various discount types offered by institutions and programs within the OTAS system. Here's a detailed explanation of its structure:

• id: Serves as the primary key, uniquely identifying each discount record with an auto-incrementing integer.
• discount_value: Stores the numerical value of the discount, which can be interpreted based on the discount_type. It's a double data type to accommodate both percentage values and fixed amounts.
• discount_type: An enum string that indicates the nature of the discount calculation. Possible values are:
  • percentage: The discount is applied as a percentage of the total cost.
  • fixed: The discount is a fixed amount subtracted from the total cost.
  • none: No discount is applied. This might be used for records that exist for administrative purposes but do not currently offer a discount.
  • inherited: The discount is inherited from another related entity, indicating that the discount calculations are derived from another source, such as a parent institution.
• type: An enum string that specifies the eligibility or reason for the discount. Possible values include:
  • siblings: A discount offered to students who have siblings enrolled in the same institution.
  • advance: A discount for students who pay for their studies in advance.
  • exclusive: Specific to programs, this discount offers an exclusive price for the program, typically as a fixed amount. This type is not applicable to institutions.
• discountable_type & discountable_id: These columns implement a polymorphic relationship, enabling the table to associate discounts with either institutions or programs.
  • discountable_type: Indicates the class type of the associated entity (Institution or Program), guiding the ORM (Object-Relational Mapping) on how to interpret the discountable_id.
  • discountable_id: Stores the ID of the associated entity, whether an institution or a program, based on the discountable_type.

This table's flexible structure allows the OTAS system to comprehensively manage discounts, adapting to various scenarios where discounts might be offered to students for different reasons and in different forms. The use of a polymorphic relationship makes it versatile in applying discounts across different entities within the system.

Levele Types

The level_type table is structured to manage the many-to-many relationship between levels and types, effectively restricting the available levels for a specific type. For instance, a University type might include Bachelor, Master, Doctorate, and Associate levels, while an Institution type could encompass Training Course, Language Course, Foundation Year levels, among others.

The level_type table comprises:

• level_id: A foreign key linking to the levels table, establishing the relationship with a specific level.
• type_id: A foreign key connecting to the types table, defining the association with a specific type.