Developing AcadeMix Backend

We need to come up with a solid structure for the backend of the AcadeMix project.

As we have discussed in the weekly meeting first we are gonna come up with the structure, identify the database requirement. After that, we will identify the necessary endpoints and post it here so that we can proceed with the UI development simultaneously.

4 Likes

Hi all,

Following are my suggestions for the database and API design.

Database

We have decided to implement 3 main entities named category, sub_category, and item I have included a design idea based around the above entities to implement a database.

Adding relational tables for each entity to map relations with a language entity.


Above I have created a new table for the three main entities(*_translation). These tables will help to match language types with the data. Even though this approach is a bit complicated and requires a few more tables, I think this is better as if someday this project expands to an international level we can add support for more languages easily without breaking the code.
Basically we get the ability to manage languages in the database.

API

For the first phase following are the APIs we are planning to develop.

  • /languages

    • GET - all languages
      Payload - [ { id:1, locale:"un-en" }, ... ]
  • /categories

    • GET - all categories
      Payload - [ { id:1, name:"Teacher" }, ... ]
    • POST - a category
  • /sub-categories

    • GET - sub-categories by category
      Payload - [ { id:1, name:"Courses on Machine Learning" } , ... ]
    • POST - a category
  • /items

    • GET - item by id
      Payload - { id:1, name:"beginners guide to machine learning", description:"Lorem ipsum ...", link:"LINK_TO_THE_COURSE" }
    • GET - items by sub-category
      Payload - [ { id:1, name:"beginners guide to machine learning", description:"Lorem ipsum ...", link:"LINK_TO_THE_COURSE" }, ... ]
    • POST - an item

These are just a few suggestions, your reviews will help a lot to reach a final conclusion. So feel free to comment :blush:.

Thank you

2 Likes

This is great progress @YohanAvishke! Love the attention to detail. One quick comment looking at the ER diagram. You might need another table to map items which falls under multiple categories. I would also have a variable in item table to have a description which can be used from us to add any additional information related to the content. What do you think?

1 Like

Hi @akshika47 thanks a-lot for the response

Good point @akshika47, in this case, it’s better to change relationships of entitiees as below


Now it’s possible to,

  1. Duplicate sub-categories among categories.
  2. Duplicate items among sub-categories.

Which automatically results in items to be duplicated among categories.


This is already included in the item_translation entity. As you can see below, we are persisting both name and description.
By the end of the day, each item will have an id, name, description, and a link

image

A new normalized diagram with the above changes is attached below, Please note that I have done some variable type changes too.

1 Like

This is awesome @YohanAvishke!

  1. Just a suggestion; Since we know we know this is gonna get translated only for 3 languages, can’t we add the translations inside the entity?

    ex: category( id , name_en, name_si, name_ta )

  2. @akshika47 Are there any use cases in which a subcategory has multiple categories? :thinking:

There is no use case for subcategory having multiple sub categories but there is a use case for an item being under more than 1 category or sub category. that needs to be represented.
for example β€œKhan Academy” could be under students section and teachers section. Similarly it could fall under maths as well as some other sub category.

cc : @YohanAvishke

Thanks a-lot @jaye

I think this is a bad designing pattern due to the following reasons,

  1. Tables will depend on language types.
    By creating a new table for translation we can pretty much have an independent table structure. Expanding the database further can be done without having to worry about changing already implemented tables.
    In conclusion we will have a more solid table structure.

  2. We will have to worry about translating every bit of data(names and descriptions) if we follow the above design. Otherwise we will have to leave unnecessary null columns.
    But by having a new table for translations, we can do implementations on top of our APIs without worrying about multi-language support. Which can be added later, so the development process will be quicker.

  3. Also I think in a nER scenario it makes more sense to create new relationships whenever possible instead of bulking all the data into a single table.

2 Likes

Hi all, after 2020-05-23T18:30:00Z's call. I have added the changes we discussed to the ERD.

@shyamal @DannyB Do you have any suggestions?

Hi @jaye and team

This is looking neat assuming below.

Every item will follow the same hierarchy. Category, Sub Category, Item.

General comment, if you are uncertain about the possibility of adding more columns later with unpredictable relationship better to use NoSQL. But this seems like a relatively predictable requirement thus using SQL would be a good idea.

3 Likes

2nd Dev Meeting on Developing Backend

Held on: 2020-05-27T15:30:00Z
Attendees: @jaye @YohanAvishke @Gravewalker

Meeting minutes:

  1. Reviewed the WIP PR on data retrieval endpoints.
  2. Had a discussion on the structure of the payload.

Conclusion
After a discussion on how the payload should structure and the best possible way to extract it in that form from the database, we came to a conclusion that we should continue using only Spring Jpa to extract data and should avoid using native or JPQL custom queries unless the particular query can’t be done with spring JPA repository. After extracting the data, to avoid JSON payload from going into a recursion we decided to use JSON ignore.

Here’s a sample payload of the category

[
    {
        id:1,
        translations: [
            {
                "id":1
                "language":{
                    id:1,
                    locale:"en-un"
                },
                name:"category1"
            },
            {
                "id":2
                "language":{
                    id:2,
                    locale:"si"
                },
                name:"࢚ාࢫࢩ 1"
            }
        ]
    }
]
2 Likes

Thanks for the update @Gravewalker,

Btw, I think It’s better if we could generate the payload like this:

[
    {
        id:1,
        translations: {
              "en-us" : {
                  "name": "Category 1"
               },
              "si-lk" : {
                  "name": "࢚ාࢫ්ࢩࢺ 1"
               },
        }
    }
]

No need to include this for the initial PR. But it’s better to have a payload like this.
cc: @YohanAvishke

Thanks.

1 Like

After the code review of the still open PR, we decided to move on from the current folder structure to a more project-oriented structure.

  • Current structure.
src/
└── main
    β”œβ”€β”€ java
    β”‚   └── org
    β”‚       └── sefglobal
    β”‚           └── core
    β”‚               β”œβ”€β”€ AdminApplication.java
    β”‚               β”œβ”€β”€ config
    β”‚               β”‚   └── SecurityConfig.java
    β”‚               β”œβ”€β”€ controller
    β”‚               β”‚   β”œβ”€β”€ AcademixController.java
    β”‚               β”‚   β”œβ”€β”€ CertificateController.java
    β”‚               β”‚   β”œβ”€β”€ MultiverseController.java
    β”‚               β”‚   β”œβ”€β”€ MultiverseLinkController.java
    β”‚               β”‚   └── UserController.java
    β”‚               β”œβ”€β”€ exception
    β”‚               β”‚   β”œβ”€β”€ APIException.java
    β”‚               β”‚   β”œβ”€β”€ BadRequestException.java
    β”‚               β”‚   β”œβ”€β”€ ResourceNotFoundException.java
    β”‚               β”‚   └── UnauthorisedException.java
    β”‚               β”œβ”€β”€ model
    β”‚               β”‚   β”œβ”€β”€ Ambassador.java
    β”‚               β”‚   β”œβ”€β”€ AuditModel.java
    β”‚               β”‚   β”œβ”€β”€ Category.java
    β”‚               β”‚   β”œβ”€β”€ CategoryTranslation.java
    β”‚               β”‚   β”œβ”€β”€ Certificate.java
    β”‚               β”‚   β”œβ”€β”€ Engagement.java
    β”‚               β”‚   β”œβ”€β”€ Event.java
    β”‚               β”‚   β”œβ”€β”€ identity
    β”‚               β”‚   β”‚   └── EngagementIdentity.java
    β”‚               β”‚   β”œβ”€β”€ Item.java
    β”‚               β”‚   β”œβ”€β”€ ItemTranslation.java
    β”‚               β”‚   β”œβ”€β”€ Language.java
    β”‚               β”‚   β”œβ”€β”€ Link.java
    β”‚               β”‚   β”œβ”€β”€ SubCategory.java
    β”‚               β”‚   β”œβ”€β”€ SubCategoryTranslation.java
    β”‚               β”‚   β”œβ”€β”€ University.java
    β”‚               β”‚   └── User.java
    β”‚               β”œβ”€β”€ projections
    β”‚               β”‚   β”œβ”€β”€ CustomCategory.java
    β”‚               β”‚   β”œβ”€β”€ CustomItem.java
    β”‚               β”‚   β”œβ”€β”€ CustomLanguage.java
    β”‚               β”‚   β”œβ”€β”€ CustomSubCategory.java
    β”‚               β”‚   └── CustomTranslation.java
    β”‚               β”œβ”€β”€ repository
    β”‚               β”‚   β”œβ”€β”€ AmbassadorRepository.java
    β”‚               β”‚   β”œβ”€β”€ CategoryRepository.java
    β”‚               β”‚   β”œβ”€β”€ CertificateRepository.java
    β”‚               β”‚   β”œβ”€β”€ EngagementRepository.java
    β”‚               β”‚   β”œβ”€β”€ EventRepository.java
    β”‚               β”‚   β”œβ”€β”€ ItemRepository.java
    β”‚               β”‚   β”œβ”€β”€ LanguageRepository.java
    β”‚               β”‚   β”œβ”€β”€ SubCategoryRepository.java
    β”‚               β”‚   β”œβ”€β”€ UniversityRepository.java
    β”‚               β”‚   └── UserRepository.java
    β”‚               β”œβ”€β”€ service
    β”‚               β”‚   β”œβ”€β”€ AcademixService.java
    β”‚               β”‚   β”œβ”€β”€ AuthenticationService.java
    β”‚               β”‚   β”œβ”€β”€ CertificateService.java
    β”‚               β”‚   β”œβ”€β”€ DataLoader.java
    β”‚               β”‚   └── LanguageService.java
    β”‚               └── util
    β”‚                   └── Status.java
    └── resources
        β”œβ”€β”€ application.properties
        └── application.properties.example
  • Suggested changes: Create a package under org.sefglobal.core called academix and add necessary files.
1 Like

Hi Team,
If you don’t mind research little bit about β€œClean Architecture”. Provide very flexible extensive approach to organize your code that may grow extensively yet can accommodate changes with less headache. :grin:

Key. Only benefits If code base grows eventually with different perspectives such as domain or features, technology changes like db or api etc.

On a different note. How do you connect on the 7pm call?

1 Like

@shyamal
You can use this meet link to connect https://meet.google.com/chs-konp-dst

@Gravewalker @YohanAvishke
Any updates from the data retrieval API?

@Gravewalker is still working on it. @Gravewalker can you please give an update. State if you’re facing any blockers.

I updated the PR with the requested amendments.
If no further amendments are needed data retrieval part of the API is completely implemented with this PR.

@Gravewalker there are still a few unresolved discussion which seems to be outdated. Can you please add comments to them pointing to the commit that fixed them.

@YohanAvishke Aiya Where are we up to now on β€œCreating API’s”? Do you need any help from my end? :slightly_smiling_face: