I designed a database scheme which purpose it is to act as a foundation for testing a System under Test (SUT) which weren't testable by automated test on default (have no Oracle and are therefore non-testable). The scheme has a Source entity which is tied to a scenario entity via an n:m relation. The those two describe the kind of input which is generated and provided to the SUT. Then there are entities which describes the output, so further automated tests can ask which output it should be. The first is a Dataset entity with a 1:n relation between scenario and Dataset. Furthermore, Dataset is related to Result via a 1:N as well.
My problem is that source and result have two attributes (resolution, site). To avoid an unnecessary redundant relation between source and result I wanted to create a View. And because I have no way of interfering with the two attributes they have different Values which mean the same thing e.g. source.site = 20 result.site = London. That's why I wanted to use a look up table (LUT) which stores both values and is only accused by the view.
Is this that right way, or should I, instead of using the view and LUT, use an n:m relation which will be represented by an associative entity?
To aid my description I added the latest image of my DB Scheme.
I hope this is clear, if not I'll happily provide more information.
The SQL which I used to create the view:
SELECT emi.id AS emission_id, src.id AS source_id, sce.id AS scenario_id, emi.* FROM scenario sce, dataset d, source_df src, map_scenario_source_df maps, emission_dda emi, df_site_discription df_site, df_resolution df_res WHERE sce.id = d.scenario_id AND d.id = emi.dataset_id AND maps.source_df_id = src.id AND sce.id = maps.scenario_id AND emi.site::text = df_site.name::text AND df_site.number_string::text = src.site::text AND emi.agility::text = df_res.agility::text AND src.resolution::text = df_res.resolution::text AND emi.normalized_center_frequency_mhz >= (src.mean_freq - 384000)::double precision AND emi.normalized_center_frequency_mhz <= (src.mean_freq + 384000)::double precision;