Have unique constraint between tables
<p>I need to validate uniqueness of an element but each element could have <code>n</code> codes and I need to validate that to. Element should be unique by type, provider, time and code. When an element is created it has a code array if any of that codes overlap with existing element code that means the element is the same.</p>

<p>E.g Having</p>

<p>codes</p>

<pre class="lang-html prettyprint-override"><code>| id     | code|
|:-------| ---:| 
| 1      | 123 | 
| 2      | 456 |
</code></pre>



<p>elements</p>

<pre class="lang-html prettyprint-override"><code>| id     | type          | provider_id  | time       |
|:-------|:-------------:| ------------:|-----------:|
| 1      | A             | 1            | 01/01/2016 |
</code></pre>



<p>codes_elements</p>

<pre class="lang-html prettyprint-override"><code>| code_id | element_id  |
|:--------|------------:| 
| 1       | 1           | 
</code></pre>



<p>Expectations:</p>

<p>When I try to insert:</p>

<pre><code>`Element = type: A, provider: 1, codes: [1], time: 01/01/2015`
</code></pre>

<p>I expect this fails because violate contrain</p>

<p>When I try to insert:</p>

<pre><code>`Element = type: B, provider: 1, codes: [1], time: 01/01/2015`
</code></pre>

<p>I expect this create a new element with code relation</p>

<p>When I try to insert:</p>

<pre><code>`Element = type: A, provider: 1, codes: [2], time: 01/01/2015`
</code></pre>

<p>I expect this create a new element because element is the same but the code array not overlap with any other element</p>

<p>When I try to insert:</p>

<pre><code>`Element = type: A, provider: 1, codes: [1, 2], time: 01/01/2015`
</code></pre>

<p>I expect this add relation in codes_elements only so element 1 will have 2 codes now because code 1 overlap other element which means that this is same element so I need to just include code 2</p>

<p>Options that I see</p>

<ul>
<li><p>Change join table to int array in elements table.- In this case I can search by other columns and append code id also create contrain by codes array to prevent duplication. Problem with this is when I query element table by code is slow e.g get all elements with code 1.</p></li>
<li><p>Create a trigger and check if codes overlap other element assign just code relation if not create new element.</p></li>
</ul>

<p>I'm using Ruby on Rails but I need model validations are not enought because I've multiple threads doing inserts at same time and could end with duplications</p>

<p>I would like to know better options or comments about current ideas looking improve performance, prevent duplication, etc.</p>