20231123_DDL_UUID - Githubissues

This tasks add UUIDs as unique index to a selected set of SEAD entity tables.

Scope

This task adds UUID to the following entity (root aggregate) tables in SEAD:

The use of UUIDs as primary keys has the folllowing advantages for primary keys compared to serial (auto-incremental) primary keys:

Global Uniqueness: UUIDs are designed to be globally unique, not just unique within a specific database. Records from two different systems can be merged without the need of reassigning IDs to prevent conflicts.
Multiple Systems: With UUIDs, a system can generate its own identifiers without needing to communicate with the others to avoid conflicts. This is not the case with auto-incrementing IDs, where you'd need to ensure that no two systems assign the same ID.
No Collision: With UUID, the probability of collision (i.e., two identical UUIDs) is extremely low, even with a large number of UUIDs being generated.
Concurrency: UUIDs can be generated in any part of your system at any time, without any database operations. This can be a big advantage if you have a high-concurrency system where many IDs need to be generated at once.

The main upsides in particular for SEAD are:

Simplified Data Import: SEAD can accept UUIDs generated by external systems which removes the need to reassign identities in new data.
Avoid Pre-Allocated Identities: Current import procedures necessitates a pre-allocation of an identity interval for incoming lookup data.
Fewer relation tables: The SEAD database schema can to some extent be simplified and generalized since entity specific relations can be replaced by global UUID relations.
Fewer foreign keys: Some forreign keys kan be replaced by global UUID relations.

The downsides to using UUIDs is mainly:

Storage Space: They take up more storage space (16 bytes for a UUID vs 4 bytes for a typical integer).
Performance: Indexing and searching by UUID can be slower than by integer.
Not Human-Friendly: UUIDs are hard to remember and type correctly.