To make a link table, do I need all the fields in the table to create one? So, I have 5 tables, calendar, person, hours, leavers and starters and I want to create a link table for them all. So, I have Date in calendar, leavers and starters and person id in person and hours. I also have person id in starters and leavers. Can I create a link field for the following and it will work?
First, you need to concatenate the tables that are the same concept, that is, people all together, the dates and times all together ... If you have multiple types of records, generates a field that distinguishes each record. Once you have that generates keys for each, uniting all in the same table, but do not call the keys with the same name, ie the key person name does not be equal to the key calendar name.
Besides, link tables are used to join multiple fact tables, if size is better not to do this but link them to the fact table.
Could you throw some sample data in an excel for us? What fields are generating the synthetic keys? Is it Date?
Without seeing what you're doing, this is how I think I would approach this:
Calendar: Date AS %Key_Date
Person: PersonID AS %Key_LeaverID, PersonID AS %Key_StarterID
Hours: PersonID AS %Key_HoursID
Leavers: Date AS Leaver_Date
Starters: Date AS Starter_Date
As Marcus mentioned, you shouldn't need a link table to stop the synthetic keys as the model looks fairly straight forward, only make sure that each field is uniquely named (could be accomplished using QUALIFY/UNQUALIFY function). But if you must, the above should work.
