SQL：如何在sql中为特定表实现多个数据的插入/更新

Question

First of all the question might be misleading for I don't know how to put into words my exact problem.

I'm planning to make an attendance system where a user would be able to time-in/time-out. I created two tables, first the user table(containing user's information) and the timestamp table(containing the time-in and time-out). Now the problem is I don't know how to implement the timestamp table to be connected to a specific user and be able to store multiple time-ins and time-outs.

Please let me know if need to clarify some things for I'm not sure if the info I've given is enough. Thanks in advance.

Answer 1

Add a unique user identifier column to the user table and make it your primary key. Add the same column to the attendance table but do not make it a primary key. It will be this common column that will allow you to relate your users to their attendance. You may also want a unique identifier on the attendance table if you are not going to set the time out until later.

CREATE TABLE  `User` (
    `UserID` int unsigned NOT NULL auto_increment,
    PRIMARY KEY  (`UserID`)
);

CREATE TABLE `UserAttendance` (
    `UserID` int unsigned NOT NULL,
    `InTimestamp` timestamp NOT NULL,
    `OutTimestamp` timestamp NULL,
    FOREIGN KEY (`UserID`)
        REFERENCES `User`(`UserID`)
);