Content
View differences
Updated by Judith Roth about 23 hours ago
So far we decided about a basic data model:
1. On table `work_packages` add a column `sequence_number`
2. Have a new table `historical_work_package_identifiers` with `project_id` (FK), `work_package_id` (FK), `sequence_number`
<br>
How will we query work\_packages?
e.g. URL: https://community.openproject.org/wp/SC-111/
1\. Options for finding the work package for SCO-111 (easy case - SCO current semantic identifier of the project)
a) there is a real column in work packages we can query (but need to update on every change of the project or project identifier)
b) there is a generated virtual column in work packages we can query (would contain only semantic IDs!)
Q: How does that work if the project&#39;s semantic ID changes?
c) there is a generated stored column in work packages we can query (would contain only semantic IDs!)
Q: How does that work if the project's semantic ID changes?
d) we resolve the project by its semantic ID and then query the work package table by project\_id and sequence\_number
2\. Find the work package for SC-111 (with project identifier being renamed from SC to SCO before it)
1. Try to find work package directly (see above) without result
2. Identify the project which belonged to "SC"
3. Query new table `historical_work_package_identifiers` with project\_id and sequence number
\---
Another use case to keep in mind (to future-proof the above approach):
How will the mobile app get the user-facing ID?
\- mobile app uses the API
\- probably the API needs a separate column \`user\_facing\_identifier\` (working name) that the app can use
- Value when instance is set to semantic: SC-111
- Value when instance is set to database IDs: 345
- Should this be another virtual column because the web app also needs to decide which value to render?
1. On table `work_packages` add a column `sequence_number`
2. Have a new table `historical_work_package_identifiers` with `project_id` (FK), `work_package_id` (FK), `sequence_number`
<br>
How will we query work\_packages?
e.g. URL: https://community.openproject.org/wp/SC-111/
1\. Options for finding the work package for SCO-111 (easy case - SCO current semantic identifier of the project)
a) there is a real column in work packages we can query (but need to update on every change of the project or project identifier)
b) there is a generated virtual column in work packages we can query (would contain only semantic IDs!)
Q: How does that work if the project's semantic ID changes?
d) we resolve the project by its semantic ID and then query the work package table by project\_id and sequence\_number
2\. Find the work package for SC-111 (with project identifier being renamed from SC to SCO before it)
1. Try to find work package directly (see above) without result
2. Identify the project which belonged to "SC"
3. Query new table `historical_work_package_identifiers` with project\_id and sequence number
\---
Another use case to keep in mind (to future-proof the above approach):
How will the mobile app get the user-facing ID?
\- mobile app uses the API
\- probably the API needs a separate column \`user\_facing\_identifier\` (working name) that the app can use
- Value when instance is set to semantic: SC-111
- Value when instance is set to database IDs: 345
- Should this be another virtual column because the web app also needs to decide which value to render?