PostgreSQL Practices of Elective Selection Relation in Colleges

By Digoal

Background

In terms of elective selection for college students, one elective can be selected by many students and one student may select multiple electives at the same time. Students and electives are in a many-to-many relationship. This situation brings about two problems:

  1. How to find the relevant electives of a specific elective and sort these electives by relevancy ( namely, other electives also selected by a student who selects this specific elective)?
  2. How to find the relevant electives of a specific elective and sort these electives by relevancy ( namely, use recursion to find other electives also selected by a student who selects this specific elective and electives selected by the students who have selected the “other electives”)?
Image for post
Image for post

Demo

Meeting the first requirement is very easy:

Consider 100 thousand students and 1000 electives. Each student selects 5 electives on average.

Let’s see how to find which electives are relevant and sort these electives by relevancy (what other electives are also selected by students who have selected this elective). Sort the electives by the number of selections in descending order.

1. Create a table to store elective selection information:

2. Insert 500 thousand records:

3. Use arrays to store electives selected by individual students:

4. Use arrays to store information about students selecting each elective:

5. Obtain all electives selected by students based on one elective they selected, aggregate these electives and display them by relevance:

6. Obtain results:

Example results

The preceding query can be performed in milliseconds when parallel computing is used.

Related Case

To implement the second requirement, a recursive method is required.

This involves a very large volume of data and therefore it takes a very long time to return results. In fact, a very deep level does not make much difference.

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store