Synthetic keys and why they're sometimes bad news
Whenever you create two or more tables that have two or more fields with the same name, QlikView creates a synthetic key table to join them.
Background
Old school logic has stated for a long time that Synthetic keys in the data model design are bad. This is not always the case, however, and in some cases, it is unavoidable.
How to do it
The best way to avoid Synthetic keys is to create a single field with all the fields concatenated together. This needs to be done in all linking tables. The result would be a cleaner and potentially more efficient design.
The problem with this approach is the amount of memory it can consume. The uniqueness of this new combined field means that the amount of memory used to store it is increased and not just in one table but all of them, as in the following simple example.
Current fields:
|
Field |
Number of unique entries |
Bytes used |
|---|---|---|
|
|
50 |
500 (10 characters x 50) |
|
|
100 |
800 (8 characters... |
