Update database documentation and review Database

[temmey]

Tasks

• [x] Review Database
• [x] Update ER diagram in doc/database/schemata/01er_diagram.md
• [x] Update table descriptions in doc/database/schemata/02table_descriptions.md
• [x] controll doc/database/hierarchy.md and update if needed

Related Pull request

644

[temmey]

@markus2330, in #667, my individual tasks involves review DB. Could you please clarify what exactly this entails? Are you referring only to updating the documentation, or do you have other expectations?

[markus2330]

The goal is that from after the release, which we want to do today, we don't need to change the database in an incompatible way, i.e., a way where we would lose data from maps we already entered etc. So the review should be if

1. there are some problems that something is not future-proof
2. database guidelines are not fulfilled

Documentation does not necessarily need to be done before the release, but writing it will probably help to uncover problems.

704 and #682 also include changes to the DB to be released, please review the migrations there, too.

[temmey]

I've made significant progress on this issue, and I've brought the documentation up to its current state. However, after reviewing the database, I've come across some challenges.

As I work through the documentation, use case by use case, I anticipate changes will occur during coding. This makes it difficult to predict which columns need to be implemented. Attempting to gain a complete overview simply by reading the use cases seems like an impractical time investment.

Here are a few issues I've encountered:

1. There are use cases where our tables need to be extended by one or more columns, which we haven't implemented yet. I could incorporate these into the ER diagram, but I question its usefulness. The developer implementing it would need to decide on the best implementation method anyway.

2. Some use cases necessitate tables that we haven't implemented yet. My suggestion would be to only add empty tables and their relations to the ER diagram. Attempting to implement columns might lead us back to the first issue mentioned above.

3. I'm finding that Mermaid.js isn't ideal for reading large ER diagrams.

What do you think @markus2330

[markus2330]

Only short answer, as I am in a hurry: For now, let us only document what we have already implemented.

What would additionally be needed for hierarchy or further use cases should be done separately.

[markus2330]

As I work through the documentation, use case by use case, I anticipate changes will occur during coding. This makes it difficult to predict which columns need to be implemented. Attempting to gain a complete overview simply by reading the use cases seems like an impractical time investment.

Yes, I agree.

There are use cases where our tables need to be extended by one or more columns, which we haven't implemented yet. I could incorporate these into the ER diagram, but I question its usefulness. The developer implementing it would need to decide on the best implementation method anyway.

I agree. The ER diagram etc. should only contain what we have implemented.

I'm finding that Mermaid.js isn't ideal for reading large ER diagrams.

Probably we shouldn't include all columns?

[temmey]

I'm finding that Mermaid.js isn't ideal for reading large ER diagrams.

Probably we shouldn't include all columns?

That may help a bit, but as we continue to implement more tables, the diagram will become so large that it'll be hard to read. We could split the graph into multiple sub-graphs, use an external service, or employ JS to improve the ER-diagram display in mdbook.

[markus2330]

I think the ER diagram should have their focus on the overview, not on the completeness. Subgraphs could be an option if there are some specific details to be shown.