Design Implementation SQL tables

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.