Analyse database design best practices

[bojanabogicevic]

In order to complete issue #11 following analysis should be done:

• Analyse whether primary key should be of type UUID or Long
• Analyse whether many-to-many relation entities should be created manually or through annotation (what happens to existing tables if additional field needs to be added later down the line)
• Describe results of analysis in the comments with pros and cons
• Organise team meeting to present results of the analysis

[bojanabogicevic]

Results of the analysis

Primary keys

When it comes to primary keys, after researching I have come to a conclusion that both options are valid but one has more pros than the other when we compare UUID and Long. It is still up for a debate.

Pros

1. Every row will have a unique primary key (Globally unique)- Chance of two UUIDs being the same is close to 0, even when it comes to two different rows in two different tables
2. Manipulation - UUID can be generated by the database itself (auto-generation) or it can be manipulated through the application
3. Security - UUIDs are unique, therefore in the case of exposing primary key in the API endpoints, if a breach happens, there is no possible way another primary key can be guessed. This would mean that if one primary key is known by the attacker (data is corrupted) other rows would still be considered safe

Cons

1. Storage - UUID takes up more storage than regular Integer or Long
2. Readability - UUID is considered not as readable as Integer or Long
3. Sorting - UUID is combination of random characters therefore it can't be logically sorted.

It all depends on the purpose of the application being built. If there is a possibility that an app is going to be accessed by a different system, then UUID would be a way to go, or if we chose standard Long (less safe) we would need to not expose the primary keys in the URL of API endpoints. Which brings up the issue of another unique identifier column that would be used for API endpoint communication. AKA we should not store primary keys of one database in another database, but use for example UUID column (this is the case of using Long for primary keys).

Many-to-many

When it comes to many-to-many relationships and the question of creating its own classes. After researching I have come to a conclusion that separate classes need to be created if we see a possibility of additional columns being added later down the line. Where two classes need to be connected to the sub-entity by Many-to-one relationship.

• In the case of switching between ManyToMany annotation and creating classes, there is no guarantee that all the data will be preserved, especially in the case of column added being nullable = false.

Conclusion:

I would create all the many-to-many relationship entities, because we are never sure if additional column will be added even if 5 years from now, and the loss of data cannot be undone and would damage the system, especially in environments like production. Better safe than sorry.