How should this be designed?

Answer 1

I would suggest a different representation.

In a nutshell you want to manage 'subscription'. There are two type of subscriptions: 'individual author' and 'channel'.

So you can define a base class called Subscription and two subclasses, 'Author' and 'Channel' respectively. A 'Channel' row should be able to maintain a 1:N relationship to the authors.

Then in the front-end, all subscription will be completed in a constant time.

Besides this design will the data maintenance issue when the constitute authors in a channel changes.

Answer 2

You could treat a channel as a meta-author, i.e. subscribing to a channel is handled pretty much like subscribing to an author. That gives you two advantages: 1. When a new author joins a channel, all users of that channel automatically subscribe to that author. 2. Maybe a user is subscribed to Isaac Newton. She then also subscribes to the channel "physicists", but later unsubscribes from that again. Removing the "physicists" subscription from the user would remove Isaac Newton as well, which is probably not desired.

Answer 3

One thing you need to ask yourself is this:

If the user selects a channel of 100 authors and that channel changes, should those changes flow through to the user? The answer to that question will largely determine the design.

If you want automatic flowthrough then don't copy authors when the user selects a channel. The design needs to accommodate a channel subscription.

In OO terms a User has zero or more Subscribables, which is a parent class to either Author or Channel. A channel has a one to many relationship to authors. The entity representation is basically the same. You just need to make what's subscribed to a parent of both entities.

If you don't want to flow through changes you need to either version the channel or do what you're doing: copying authors from the channel into the user's subscriptions.

Answer 4

My approach would be to have People table with both Users and Authors and the Author table to have only People keys of Authors. Then Authors would a Members table that would resemble your linking table.