Database modeling. Many to Many relationships. Link tables

Question

Consider a database table for user info like:

id, name, email

now consider I have another table for video something like:

id, name, description, length

Say a many to many relationship exists between them based on subscription. Obviously, to handle the relationship you'd create a third link table user_video_link:

id, user_id, video_id

I suppose anyone would model the user table with User entity class and similarly the video table with Video entity class and let the database persistence framework handle the relationship between them.

Now consider this, a requirement arises that there is an option for the user to set that they are not interested in particular video. Where would I save this info to best fit my architecture? I wouldn't imagine changing basic structure of both user or video would do anything. Let us look at few options with the link tables:

1. I could create another link table, something like say user_uninterested_video_link:

id, user_id, video_id

which seem like the way to go based on how the whole architecture of the system goes but then again, there must be an elegant way, thereby this question.

2. Another way that seems tempting is to not created another link table, as suggested in 1., but to created a boolean field in the original link table as a flag that tells that the user is not interested in a particular video. This approach seems plausible because we have an opportunity with the link table to fine tune our grip on a particular link between the user and a video, one to one, rather than being lost at the greater picture, the many to many relationship that exists. My link table would be something like:

id, user_id, video_id, interested

But by going with this approach, what we see is that when it comes to modelling the database tables into our entity classes, we have to model the link table as well. Further, when something similar comes up, we start adding fields to the link table and somehow the table intended to be a basic link table ends up becoming the table that holds every business logic, something of a god table of some sort. Also, we become so dependent on database schema to program that each time any change comes up, we'd have to go through it to implement the change. The use of frameworks for persistence goes out the window and we start tweaking every bits of the database ourselves. Having said all that, do we really have to?

There must be something I am missing. Any comment on this question from an experienced craftsman would be much appreciated. Please enlighten me. Namaste.

Answer 1

Let us ignore the fact your solution 2 is not semantically equivalent to the first one (since it will allow only to model the status interested for a user who has a subscription for that particular video). Instead, let me directly respond to your last paragraph:

But by going with this approach, what we see is that when it comes to modelling the database tables into our entity classes, we have to model the link table as well.

That is correct (and IMHO not unusual).

Further, when something similar comes up, we start adding fields to the link table

This might happen.

and somehow the table intended to be a basic link table ends up becoming the table that holds every business logic, something of a god table of some sort.

No. When you have additional requirements, you need to add them somewhere at the right place in your data model, this has nothing to do if the right place is a link table or somewhere else. And if tables get "too large" or start violating normalization rules, at some point in time you will have to refactor them for not becoming "god tables". This is true for any table in your system.

Also, we become so dependent on database schema to program that each time any change comes up, we'd have to go through it to implement the change.

When an additional requirement has to be added, you will have to change something in the system, that is quite obvious. Sometimes this involves database schema changes, sometimes not. Sometimes it is possible to model a database in a way to keep the risk for changes lower than by a different modeling. But it is neither possible nor desirable for getting that risk to zero. Instead, learn about schema migrations and how such transitions can be handled with your chosen toolset most smoothly. There have been tons of questions asked here on this site and on Stackoverflow about this topic.

The use of frameworks for persistence goes out the window and we start tweaking every bits of the database ourselves.

Persistence frameworks don't do any database modeling for you. And standard changes to a data model which require additional attributes or tables should be manageable in context of any sensible persistence framework. Such changes should be quite orthogonal to the use of such a framework.

Your example shows a situation where the "automatic link table generation" feature of your favored framework cannot be used any more. But just because there is one minor feature of your framework not applicable does not mean the framework becomes suddenly useless. That framework has still 50 other useful features. Having to model link tables by yourself is no definitely no valid reason not to use a persistence framework.