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:
- 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.
- 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.