Hello. There are three tables (two with data and one for many-to-many communication):

users: id, name, email (уникален); groups: id, title; group_users: id_user, id_group;

Data is added to the users table, from a file or from another table. For all new entries, links are added to the same new group. In the data records with email are already available in the users table. In this case, you only need to add a new connection of the old record to the new group.

Is it possible to implement this using MySQL? If so, how to make it more elegant? Thank.

UPD:

There is an N-th number of new users, all of them must be added to One New Group. If a user already exists (checked by email), then add an existing user to the new group, the same as new users.

Title groups is not unique, although it does not matter.

Ts sub-question: is it possible to insert into a table with a connection with a single query (without bothering about duplicates for now)?

  • update not suitable? - Gorets
  • Those. new users.name and users.id get lost? or what data comes when adding new records? - alexlz
  • Business rules are not completely clear: can the same user belong to several groups? - BuilderC

1 answer 1

If I understand correctly, when importing users, a group is created for each user, through which access is then distributed. Those. before inserting a new user, a new group is inserted. Let it be inserted, we save its id.

When adding a new record to users you need to handle a database exception that will create UNIQUE constraint by e-mail. When it is received, do nothing (the user already exists), but immediately after that execute update group_users for the user via this e-mail to add it to the new group.

Like so. :-)

  • one
    Wonderful. TC asked "how graceful" and the people begin to fantasize. And there would be no topic-starter explain more specifically. Those. if the input tuple is: (username, title, e-mail), then so. For a new user, posts are created in users, groups, group_users. If the e-mail is already there, then the record in users is skipped (the name is dropped?). groups.title is unique or not? Etc. But this is apparently highly confidential information ... - alexlz
  • one
    @alexlz, my God, do you suggest first to torment the database with a request for a user, and then add or not? It would be better not to fantasize themselves. I wrote exactly what needs to be done. Or did I have to write down the implementation? :-) - orkaan 2:07
  • one
    @orkaan I do not offer anything. I simply note that the task is incomprehensible, and it is desirable to clarify these points before waiting for reasonable advice. By the way, do you have a base - is it scratching or biting if you start torturing it? Terrible thing. - alexlz
  • @alexlz, do not make a request before making modifications. This is an extra overhead of a pretty simple business process. - orkaan
  • @orkaan Persuaded. I will not make any requests. I listen to / read the advice of true believers with great attention. - alexlz 2:42