Quick (and hopefully easy) Synthetic Key question. Here's my issue:
I have three tables [Applicant], [Applicant Child], and [Gender]. Applicant and Applicant Child are both normal entities while Gender is essentially a reference table. Both Applicant and Applicant Child have the field "Applicant ID" in them for relating to one another, but they also both have "Gender ID" so we're able to determine the Gender of both Applicants and their children. This creates a synthetic key with Applicant ID and Gender ID.
Is there a way to remove this Synthetic Key? Should I?
Synthetic keys are not that wrong if they happen on purpose and can be kept safely on the model without any significant impact on performance. There is no need for a composite key which basically requires a maintenance that otherwise QlikView does automatically.
However, if the fields are named alike but the values do not correspond (for example, Gender is Male/Female in one case and M/F in the other, and ID contains customer ID in one case and gender ID in another) the resulting synthetic table would be a cartesian product of all possible values, due to a poorly designed data model which is what it causes performance issues and problems with selections.
If I alias both GenderID fields, won't it break connection to the third table that holds the reference value? Or, how would I point the GenderID FK to the third table to get the corresponding "M" or "F"
A quick link table might just help clean up the data model, I would personally choose to do this because I like the model to be synthetic key free for my own sanity! Might be overkill though
//-----ApplicantChild Table-----
ApplicantChild:
Load rowno() as %ACKEY
"ID" as [Applicant Child ID],
"APPLICANTID" as [Child Applicant ID],
"GENDERID" as [Child Gender ID]
Resident [OSUSR_H0L_APPLICA4];
//-----Applicant Table-----
Applicant:
Load as %APKEY
"ID" as [Applicant Applicant ID],
"GENDERID" as [Applicant Gender ID]
Resident [OSUSR_H0L_APPLICAN];
//-----LinkTable-----
Link:
LOAD %ACKEY, [Child Applicant ID] as [Applicant ID], [Child Gender ID] as [Gender ID]
Resident ApplicantChild;
concatenate
LOAD %APKEY, [Applicant Applicant ID] as [Applicant ID], [Applicant Gender ID] as [Gender ID]
.png "Miguel_Angel_Baeyens"){kind=avatar}
