CODEWITHSUNDEEP
sqldatabase-designdenormalizationredundancy
Quintin Par
Quintin Par

Reputation: 16252

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.

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

Upvotes: 1

Views: 1484

Answers (2)

Luchian Grigore
Luchian Grigore

Reputation: 258558

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

Upvotes: 4

John
John

Reputation: 721

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.

Upvotes: 2

Related Questions