S
S
SemihalDevs2016-10-30 23:23:35
MySQL
SemihalDevs, 2016-10-30 23:23:35

How to design a database for publishing news?

Good evening, experts and colleagues, when designing a database, I had a couple of questions:

  1. Each user can publish news on the site, the amount of news after some time can become quite large. So, each user needs to display the number of records published by him - constantly count them in the form of SELECT COUNT (...) or add an additional field to the users table and increment the value?
  2. How to do the same with all the news? Keep the total number of news from all users in some table entry, or constantly count in the form of SELECT COUNT(...) ?

Sorry for the stupid questions, I'm new to this and need some help.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vitaliy Orlov, 2016-10-31
@SemihalDevs

1) For each user, a field in the table in which to write SELECT COUNT WHERE user_id at the time of updating the news (added/deleted/changed status). Under simple conditions, you can make a trigger in the database, which will do it itself. Don't forget to index by user_id
2) Yes, it's better to cache this value. This is done in different ways, but the point is the same - to reduce the number of calls to SELECT COUNT
SELECT COUNT (id) and even SELECT COUNT (id) WHERE user_id = on a small attendance is not the most difficult operation .. However, if you, for example, decide to sort users by count -vu news, for example, choose top 50, then having additional. field with the number of news, it will be much easier to do this ..
In general, you think correctly.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question