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.
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 .
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?
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
A new normalized diagram with the above changes is attached below, Please note that I have done some variable type changes too.
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.
I think this is a bad designing pattern due to the following reasons,
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.
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.
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.
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.
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.
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.
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?
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.