[Suggestion] Database Schema Changes

[diogotcorreia]

Below are some changes I'd like to make to the database schema. These need to be discussed in the first HS Inventory meeting.
I'll tick them off as they're done. I'll remove them from the list if, during the meeting, they're rejected.

• 1. Change all table and field names to English. This should be obligatory since we have non-Portuguese people working on this project.
• 2. Delete the item and ferramenta tables. They seem completely useless right now. I guess they were intended for something else, so this needs to be discussed.
• 3. Maybe the estado table could be deleted and the id_estado fields converted into an enum. Depends on which values this should hold.
• 4. Add a field for material quantity. I guess if you have 10+ of a material you're not going to be creating 10+ database rows, so it'd make sense to add a quantity field to both requisicao and material.
• 5. Remove the access_token field from the membro table and put it into it's own table, along with id_socio, expires_at and device (OS name; device type; to be further discussed) fields. This would allow for long-lasting refresh tokens combined with JWT (see [Suggestion] Authentication Flow #2 for a more in-depth explanation).
• 6. Add a picture entry to the membro table, which is loaded from the Fenix API, which sends the profile picture in a base64 encoded png image. Add button to sync from Fenix and allow custom photos.
• 7. Add a role field in membro or a permissions table, which lets some members execute admin actions.

To better manage the database schema, and if we're going to use NodeJS, I suggest using Knex.js, which manages database schema migrations for us, making it really simple to change schemas down the line.

[Hugo-Marques-work]

[Hugo-Marques-work]

1. Hard agree.
2. I made that decision too ^^
3. Could be positive to not have it as an enum since new states can appear that we were not expecting. Adding new states would be easier. There can be a discussion for this tho
4. I agree and don't agree at the same time since we might want to have IDs for certain material (arduinos?). TO BE DISCUSSED
5. Can't comment for now
6. If it's sent by fenixAPI why would we save it on our db?
7. Agreed