I was wondering if anyone can help me with regards to creating Option list. Whats happening in my organization is there are set of metadata values
Department is interdependent on Location. Operation is dependent on Department and Process is interdependent on Operation. Whats happening in my case is per say there is a department value ABC in a location called 1 and there is another department value called ABC in location 2. The operation values falling under both these departments are showing up in checkin page.
I wanted to see if anyone there can help me break this relationship.
Essentially, if I select ABC belonging to Location 1, only values for that location and department should show up and not the values for Location 2.
Its like there are numbers 1,2,3,4
4 should depend on 1,2,3.
3 should depend on 1 and 2
2 should depend on 1
Any suggestions will be greatly appreciated.
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.