I am trying to solve a data design problem that has been plaguing me. I would appreciate any suggestions on how to solve this. The data is system mapping information. Source1.field1 feeds Source2.Field1 feeds Source3.Field8. I want to be able to report on the fields/sources and find out what correlated fields are up and downstream from it. I have tried various Hierarchy models but can't quite figure out a solid way to model this data without getting circular tables. See the below links for samples I have attempted but haven't managed to implement with my specific solution:
I want to be able to select a field and know what fields source or feed data to/from it. There are different use cases where i select a field and want a record for each mapping/path that includes that field, displaying comments about the individual fields and comments on the individual mappings. A path could have 5 fields: 1:2 would have comments, 2:3 would have comments, 3:4 would have comments, 4:5 would have comments. There could also be indirect mapping comments, so there could be a mapping record of 1:4 with comments on that mapping as well.
I'm not sure i'm being particularly descriptive and I recognize it's a morass that should be better defined to help figure out the data model.