Minimum multiplicity 1..* in a class diagram

Question

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?

Answer 1

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

• check F1's result
• if it's 0, throw error
• insert your T1 record
• insert the associations

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.

Answer 2

The key here is indeed the creation process: you can't enforce the minimum multiplicity without providing at the creation of T1 a reference of at least one T2.

Approach with a relational database

In a relational database, considering that the association is many to many, you would need an additional association table to implement your model. The legit creation of a T1 would then require a transaction made of an insertion in T1 and and insertion in the association table. If any of the two insertion would fail, the transaction would be rolled back, thus ensuring a clean situation. Enforcing referential integrity in the association table can ensure that either T2 exists or the whole creation is rolled back. The above mentioned approach shall be encapsulated in a procedure (at database level, or at application level), one of the parameter being the first T2 reference.

The weakness here is that it would always possible at DB level, short-circuiting database and application procedures to insert an ill-constructed T1 without T2 reference. The only way to protect against this is not to give direct DB access and make sure that DB access occurs via a service layer.

Approach with a NOSQL database

With a document oriented database, you do not need an association table: The collection of T2 references are directly in a collection of the T1 record. This makes it easier to check at application level:

• JSON Schema allows to enforce a structure taking into account the number of elements in a a collection.

• Existence of the elements in the collection must be cross-checked though.

Perhaps there are some NOSQL vendor, that meanwhile allow to add this kind of check at db level. After all document DBs are mostly based on JSON and it'd be easy to call some JS function at db level.

The review of your options

• Option 1 is similar to the one described. But without having the operations enclosed in a transaction**, there is an inconsistency risk,** e.g. the following real-life situation:

1. Check that T2 exist 
2. Someone else deletes this T2 in a parallel process and the deletion occurs just after your successful check
3. You do the inserts, but the insert in the association table fails due to now inexistant T2
4. Unfortunately T1 was already inserted, so the DB is in an inconsistent state until you remove the T1 (and removal could fail for other reasons, for example because other users have meanwhile used T1 in other tables/operations).  

• Option 2 looks like a perfect solution. It assumes nevertheless that you can insert into two tables with one INSERT operation for the trigger to work. I may be a little bit outdated, as I did not follow all the latest SQL developments anymore, but I think that INSERT into a VIEW still requires either that it's a single table view or that the insert specifies explicitly a set of columns belonging to a same table. But if you work with a DBMS that broke this barrier, let me know in the comments.

• Option 3 is used in many (older?) production systems. It is valid if you control very well the apps connecting to the db. The old view that many applications share their data via the same database does not scale well however. It only works if all applications are behaving well. The risk of inconsistent db state grows exponentially with the number of apps connected and the number of teams involved. The more recommended approach is to hide the db behind a service layer, and apps use the service layer. But this brings us quickly in more architectural questions that go far beyond your question.