databaseumlclass-diagrammultiplicity

Minimum multiplicity 1..* in a class diagram

enter image description here

In a database how could I enforce the minimum participation constraint?

in the T1 schema when you insert it must have at least 1 association with T2. To enforce this I had thought of various solutions but I don't know if they are actually considered valid.

  1. Create a procedure from which you check if T2 exists, if it exists then you insert T1 and then the association table T1-T2

  2. Create a view with T1 inside and the link between T1 and T2, I create a trigger that activates on the view upon insertion and the data of T1 and the key of T2 are inserted.

  3. Don't pay attention to the insertion but manage it at the application level if you use an app to connect to the database

Are these valid options to enforce the minimum participation constraint 1 or are there other options?

Solution

  • So, T1 and T2 are many-to-many tables. This means that you have a mapping table, let's call it TM where you store the associations.

    Now, if T1 needs to have at least one association to T2, you actually need to insert T1 before it has any associations. As a result, you need to determine the count of ids to be associated with the new T1 record before insertion. Sometimes that's not easy, so I would recommend the creation of a stored function that would determine the count of associations that would be added to T1, let's call it F1.

    And then create a procedure that

    Then, create triggers on TM for update and delete orphans

    delete T1
from T1
left join TM
on T1.id = TM.T1_id
WHERE TM.id IS NULL

    Sometimes you are not doing individual inserts, but bulk inserts. In such cases it may make sense not to have/call the stored procedure we discussed earlier, but to run the orphan removal command from above after the insert operations complete.

    Finally, you could decide to do it with the application, with a cron job that periodically runs and removes orphans if you want to make it very simple and are not worried with orphan T1 records existing temporarily.