0

Say a news website has news categories such as Politics, Business, Society, Education, Health, Tech, Celebrity, Lifestyle, ... etc. The number of categories may be more than 20. New categories may be added; existing categories may be deleted.

If a registered member has no desire to see the news of a certain categories, he/she should be able to block those news so that those news don't show up in the news list on the homepage. This will only affect himself/herself, of course. Other members have their own preferences.

Each member can visit the Manage News Categories page to block/unblock news categories. The page will contain a HTML <form> with <input type="checkbox">s of news categories.

What's the most performant database design for such a feature?

I'm currently not an expert of database design. My initial thought is creating an associative table called "member_category_map" and associate it with the "members" table and the "categories" table.

Not sure if I'm being naive, but I'm worrying that the "member_category_map" table might end up containing too much data. Assuming that there are 20 categories and each member only blocks 1 category. If there are 2,000,000 members, then the "member_category_map" would contain 2,000,000 * 19 = 38,000,000 data. I don't know if that's good for performance.

Advices are much appreciated. Thanks.

3

You need four tables:

User            Category           Article                  Pivot
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+
| id | ...      | id | title |     | id | cat_id | ...      | user_id | cat_id |
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+

Table Pivot is standing for M:N relation and contains the subscription pairs user:category. Each user can have 0..n categories subscribed and each category can be subscribed by 0..m users. To fetch articles accordingly to the user's subscriptions you need to use JOINs like that:

SELECT u.name  AS UserName
     , c.title AS Category
     , a.id    AS ArticleID
     , a.title AS ArticleTitle
     , a.date  AS ArticleDate
     , a.url   AS ArticleLink
     -- everything you need 
  FROM user      AS u
  JOIN pivot     AS p  ON p.user_id = u.id     -- pivot to user first
  JOIN articles  AS a  ON a.cat_id = p.cat_id  -- articles and
  JOIN category  AS c  ON c.id = p.cat_id      -- categories to pivot then
 WHERE u.id = 12345
 ORDER BY a.date DESC
 LIMIT 10
;

For the best performance you need to index tables by the columns used by the query:
user should have the primary Key (id)
article should have the multicolumn key (id, cat_id, date)
pivot should have the multicolumn primary key (user_id, cat_id) with FKeys to the corresponding tables.

  • Thanks. You mentioned that article should have the multicolumn key.... I guess you mean "multicolumn primary key"? I don't quite understand why article needs multicolumn primary key, since it already has id column (which can be the primary key). Could you please explain? – Ian Y. Jan 28 at 14:14
  • 2
    @IanY. Multiple columns from the Article table are looked up at the same time when query is performed. Some of them are looked up explicitly like article.date in the WHERE and some are looked up implicitly like article.cat_id in the JOIN..ON clause. The proper key should contain some/most/every column looked up during the query proceeding. Exact key is depend on the data cardinality/density/selectivity. – Kondybas Jan 28 at 14:26
  • Thank you for explaining. I now understand a bit more than before. Would you mind recommending a source/tutorial for further understanding this concept --> "The proper key should contain some/most/every column looked up during the query proceeding. Exact key is depend on the data cardinality/density/selectivity."? – Ian Y. Jan 28 at 15:37
  • 2
    Rick James collected a huge pile of the useful info and summarize it a lot. Refer to his page - mysql.rjweb.org – Kondybas Jan 28 at 16:06
  • Thanks for the resource! And also thank you for illustrating the table design with a useful query example. That really helps me understand why the tables/database are designed in that way :) – Ian Y. Jan 28 at 16:18
3

My strongest suggestion is...

  • Once you have a few thousand 'users', throw away the schema and start over.

Seriously. After you have experience with your application, you can formulate more specific questions, hence get more specific answers. And you will know what the pain points are.

I have gone through similar things in the past, I have written about them. (Thanks to Kondybas for the mention.)

If you follow those tips, you may be able to postpone the schema rewrite until after 10K users.

  • Thanks Rick. These guides really broaden my horizons :) – Ian Y. Jan 29 at 4:12

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.