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,
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;
  • Do I get you right that you wanr results to be a view? If yes, which of the several sources that one scenarios is associated with via mappers should be used? – Laurenz Albe Jul 7 '20 at 7:00
  • no. I want to use the view to simplify the SQL Querries so when useres ask for every result which comes from a specific source the querrys are simpler. And only when this view is created, or updated, the LUT is accesed, so my question was : Is this okay, or should I make it real n:m relation between Sources and Result? – Chris Jul 7 '20 at 13:05
  • Thank you. So, the trigger is there to update the view if anything changes, right? – Chris Jul 7 '20 at 13:09
  • Sorry. I don't understand the question. – Laurenz Albe Jul 7 '20 at 13:15
  • No Problem, I'm new to this so my knowledge of this topic is sometime not adequate enough to fully convey my intentions. My Questions are: "Is the use of the View in the depicted scenario, considered >Best practice< ?" "Should there be relations between Source and Results, since the LUT uses values of both to make them comparable?" "And If so, would that defend the purpose of the View?" Since you removed the comment about the Trigger, I guess it wasn't relevant right? Oh, and I'd have used a materialized view if you had brought up the Triggers. – Chris Jul 7 '20 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.