This content has been marked as final. Show 2 replies
could you please elaborate on your data model? Namely, what columns do you have in each table and with what values?
If Location has just one column called let's say +[locationId]+ - values , , , ... you could use a surrogate primary key +[locationKey]+; the value would then look as [1, 1], [2, 2], [3,3] (this looks stupid at this point, but you will get the idea soon).
Then, if Department has two cols +[locationId, deptValue]+ like [1, ABC], [1, XYZ], [2, ABC], [2, DEF], ... you could introduce a surrogate primary key +[deptKey]+ and use +[locationKey]+ rather than +[locationId]+ - +[deptKey, locationKey, deptValue]+ like [1, 1, ABC], [2, 1, XYZ], [3, 2, ABC], [4, 2, DEF]
Let Operation be the third table. I guess that you used just +[deptValue]+ as the foreign key (that's why you got duplicates) - in fact, you'd have to use +[locationId, deptValue]+, but now we can replace it with our surrogate key +[deptKey]+ - the Operation table will have again three columns +[optKey, deptKey, optValue]+ with values like [1, 1, op1.ABC.1], [2, 1, op1.ABC.2], [3, 2, op1.XYZ.1], [4, 3, op2.ABC.1], [5, 3, op2.ABC.2], [6, 4, op2.DEF.1] and so on.
I guess you have already gotten the idea...
Note that apart from tables, you will have to define views and relations accordingly.
Thanks Jiri. I will be trying this out. I greatly appreciate your timely help.