Relationships midst two tables?

I have table visitors and table users. Each visitor can be created by only one user and contains field visitors.userId.

So, any another user from table users can edit or delete one or more visitors.

I have created a third table events_log for logging actions like edit/delete:

Visitors_log

id | userId | visitorId | action

This table stores information about user who did actions over table visitors.

Which relationship should be between table visitors_log and rest two: users and visitors?

Now I have this Db scheme:

My opinion is: visitors_log can have one or more rows. So relations is one to many. In the same time visitors_log can contain one or more users. So, where I am wrong? Now it is one to one relation.

Answer

Is there a possibility that the user_id of a visitor could ever change ?

If the answer is yes, then you would better take the safe approach and have two foreign keys in the log, referencing the concerned visitor and the user that commited the action.

In MySQL :

CREATE TABLE visitors_log (
    idVisitorLog INT AUTO_INCREMENT,
    idVisitor    INT NOT NULL,
    idUser       INT NOT NULL,
    action       VARCHAR(100) NOT NULL,
    date_action  DATETIME NOT NULL,
    PRIMARY KEY (idVisitorLog),
    FOREIGN KEY (visitor_log_idVisitor) REFERENCES visitors(idVisitor),
    FOREIGN KEY (visitor_log_idUser)    REFERENCES users(idUser)
);

PS : you probably want a date column too in the log table, I added one.

---

On the other hand, if the user a visitor is assigned to can never change over time, then your design can be simplified. In the log table, you could just store a foreign key to the visitors table. As the visitor stores a (fixed) user id, you do not need to duplicate that information in the log.