DeNormalize Many to Many but don’t want to store redundant data in SQL world

Say I have newsletters and then subscribers:

Whenever a user subscribes to a newsletter we need to store this in a table - the subscribers list.

Should I store this in the newsletter table or in the user table? I.e. should the newsletter store the list of subscribers or should the user store the list of newsletters he has subscribed too? Both the cases will be widely used. User will need to show the newsletters he has subscribed too and newsletters will need to show the users subscribed to it. 开发者_JS百科

How can I design a table structure for optimized for reads? I don’t want to go the NoSql route.

Are your Users and Subscribers tables the same? If not they should be. The Users table should contain your users, the Newsletters table, your newsletters and your Subscribers the relation between the two.

Say you have:

Users

user_id    name
1          a
2          b
3          c

Newsletters

newsletter_id   name
1               x
2               y
3               z   

Subscribers

user_id   newsletter_id
1         1
1         2
2         2

User a is subscribed to x and y, user b is subscribed to y. You should also add indexing after user_id and newsletter_id to the Subscribers table and the PK should be (user_id,newsletter_id).

This is what many-to-many tables are for. You store the relationship between the two in a separate table. Example:

newsletter
 id,
 name,
 etc.

subscriber
  id,
  last, 
  first,
  etc.

newsletter_subscriber
  id,
  subscriber_id,
  newsletter_id,
  other attributes, etc.