CODEWITHSUNDEEP
c#sqldatabase-designprimary-keydatabase-table
Afnan Bashir
Afnan Bashir

Reputation: 7429

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?

Upvotes: 0

Views: 103

Answers (3)

sclarson
sclarson

Reputation: 4422

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

Upvotes: 1

xyz
xyz

Reputation: 1497

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.

Upvotes: 0

Stu
Stu

Reputation: 15779

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.

Upvotes: 1

Related Questions