Lock pages in co-editing

0 like 0 dislike
11 views
Good time of day.

There is a database structure

image

Brief description:

users — the user table.

user_roles — user role in a specific wedding, the user can participate in editing
several weddings.

wedding — table weddings.

user_edit_rights — user Rights in a particular wedding to edit a module.

user_module_locks — Blocking the user-specific module

user_right_modules — a list of modules which can be reactivate.

modules — lists all modules on the site.

The goal is to make lock individual pages for collaborative editing,
that is, that the page could only be edited by one user.

I decided to do this:

1. After the user comes to the page executes the following query.

SELECT

users.user_id,
modules.module_id,
IF(user_edit_rights.user_id = users.user_id, 1, 0) AS user_can_edit_module,
my.module_id AS user_lock_module_id,
IF(other.user_id != users.user_id,1,0) AS another_user_lock_module

FROM users

INNER JOIN user_roles ON users.user_id = user_roles.user_id

INNER JOIN wedding ON wedding.id = user_roles.wedding_id

LEFT JOIN modules ON 1

LEFT JOIN user_edit_rights ON user_edit_rights.user_id = users.user_id AND user_edit_rights.wedding_id = wedding.id AND user_edit_rights.module_id = modules.module_id

LEFT JOIN user_module_locks AS my ON my.wedding_id = wedding.id AND my.user_id = users.user_id

User_module_locks AS a LEFT JOIN other ON other.wedding_id = wedding.id AND other.module_id = modules.module_id

WHERE users.user_id = 3285
AND wedding.id = 72
AND modules.name = 'gifts'


The result looks like this:

user_idmodule_iduser_can_edit_moduleuser_lock_module_idanother_user_lock_module
328510020


After that I can either add a record to a table user_module_locks
either update or delete unnecessary blocking. That is further
the application logic depends on the result of executing this query.

Embarrassing me 6 dainow (ideally 8-9) and the constant need to run this query
and request manipulacao (update, insert, delete) data in the table user_module_locks
the data in this table will constantly change when the user navigates from one
page to another. The EXPLAIN of this query shows that everything is SIMPLE and most likely
simultaneous editing by many users will not (online < 10).

I'm most interested in the question whether it is normal when for complete
sushnosti uses a single query with a large number of CMV joynow?

As well as an approximate algorithm for the organization of editing data in multiple
users at once.

And also is it normal when the primary key consists of 3 or more fields?
by | 11 views

2 Answers

0 like 0 dislike
Yes, everything is normal, and a lot of joynow, and keys, when used relatlona base object models. Make the materialization, at the expense of data redundancy create a comfortable conditions for popular selections, or use key-value storage.
by
0 like 0 dislike
The user can edit any particular wedding, while not being a customer or as an administrator or Manager? If Yes, then following questions:
\r
— who issues permits for the right to edit
— is it possible permission to make the abstraction of the DB module (it is possible to write abstractions in the interface)
\r
More questions
— user roles change dynamically? There is a high confidence that it is necessary in this form? If Yes, then why not add a role as a version integer field, and just keep it description — why a separate table for them?
lock — a wedding? If Yes, then why not create a single table with benefits?
\r
In General, you're right — itching... to optimize and to optimize. However, there is little doubt that you have something very useful to advise on the information provided. Councils will be much more objective when looking at TK.
by

Related questions

0 like 0 dislike
1 answer
asked May 1, 2019 by Timbeik
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
asked May 4, 2019 by titov_andrei
0 like 0 dislike
3 answers
110,608 questions
257,186 answers
0 comments
32,830 users