Design Implementation SQL tables

I am new to SQL and been given a task. Following are the details:

What I have:

1. A C# desktop application for User login and view user status (only two options: user logs in and check status of all users that are dummy created)

2. A table named USER containing

   • id
   • username
   • datecreated

3. A table named LOGINSTAT containing

   • id
   • username
   • Logtime
   • logDate

What I have to implement

I have to save time and date when ever user logs in in LOGINSTAT table using SQL.

My question

My question is how can I implement that. I can do the coding part but I am interested in getting some good advice to implement it. I think of it as a formal way as I know to do it:

when user logs in insert values into the login table giving all the required values.

BUT

I think that might be a bit odd. Some of my friends said you may be able to implement it 开发者_开发百科by use of foreign key and primary keys, but the problem lies that the user may log in many time in a day. How to keep track of login time and date in that case?

---

You don't need username in your LOGINSTAT table.

You'll probably want the LOGINSTAT to include:

• id
• u_id
• loginDateTime

id is the unique ID of every login
u_id is a foreign key from the id in users that matches your log event to a user
loginDateTime is a datetime that will give you both your log date and log time in one column

---

What is unique in LOGINSTAT? Not user by itself, but ID+LogDate+LogTime should be. That would be your primary key.

The only foreign key is in LOGINSTAT: ID, which references the ID in the USER table.

---

Values in a PRIMARY KEY column (eg. USER.id) must be unique from one another.

Values in a FOREIGN KEY column in another table referencing that primary key (eg. LOGINSTAT.id referencing USER.id) do not need to be unique - you can have multiple records in a table have the same foreign key column reference the same primary key.