N
N
Nikita Krasnikov2020-07-17 21:10:01
MySQL
Nikita Krasnikov, 2020-07-17 21:10:01

How to store data available to certain users?

DB: percona mysql 8.3
Backend: php7. 2

I have a point table where information about the point is stored: address, code, coordinates, flag of general availability (enabled)

Now there is such a case that a certain set of points can be available only for a certain number of users.

For example, there are 2000 points that are enabled=0 for all users, but for users 22,23,54,88 should be available, i.e. enabled=1.

Considered options:
1. A separate userPoint table, where there will be userId, pointId columns. According to this table, it will be easy to select these points through join. But the table will swell very quickly, because. we have about 40,000 points and more than 2,000 users for whom these points will be available, but not for the rest, in total we get 8 million rows and over time the table will swell even more.

2. A separate userPoint table, where there will be userId, pointIds columns. The pointIds column will contain a JSON array with point IDs. On the backend, we will pull out points with 2 queries. 1 query pulls an array with points from the userPoint table, 2 query gets select points.

3. The same table as in option 2, only make 1 request on the backend, an example of a request:

SELECT id FROM point WHERE id IN (SELECT j.pointId
FROM userPoint uP
JOIN json_table(
  uP.pointIds,
  '$[*]' COLUMNS (pointId INT path '$')
) j WHERE uP.companyId = 9315)


4. Considering the option to transfer such data to mongodb.

Tell me which way to go? Which option to choose or suggest your own.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question