I currently have a situation where I have a number of different flat files which have 4/5 common fields.
My main table has 4 tables hanging from it. But these hanging tables all have 4/5 common fields. I attached a picture with the tables. Im just wondering what would be the most efficient way in dealing with this issue. I dont want to have any synthetic tables/loops in my structure.
Is using a link table the best way to do it? How can I implement this?
What are the advantages/disadvantages of concatenating all fields into one table?
Concatenating is an easier way which has no problem except that you need to be vigilant about null values in your document. Secondly your information density for certain fields is reduced which means it takes more time to compute eventhough its only minor.
Ideal way to deal with synthetic keys is that you need create them your self. What I mean is that create link tables for these fields using autonumber function.