SQL Server database design users and events tables relation

Question

I have 2 tables Users and Events.

The Users table stores information about the users: name, email, etc. The Events table has a list of events: registration, password reset, etc etc.

How can I map these two into a new table where I can store history about user activity.

Example user reset password, add a record to a third table called UserEvents for example.

My problem is building this 3'rd table. So if I add to it a composite key userid and eventid, that won't be unique. as an user will reset a password multiple times, and will end up with a composite primary key of same userid and save eventid.

User Table has the following columns:

• UserId (PK)
• Name
• Email

Event Table has the following columns:

• EventId (PK)
• Name
• Description

Should the UserEvent table be like this:

• Id (PK) - generated or identity type
• UserId (FK to User table - UserId)
• EventId (FK to Event table - EventId)

I will use EF7 code first approach.

Answer 1

@gokaysatir Databases with millions of rows isn't really a good argument to use NOLOCK. Millions of rows database are quite normal. If users can not login when you don't use NOLOCK, will certainly caused by blocking because of other (data modification) sessions. If you really need NOLOCK so you're users can login, there is definitely something wrong in you'r design or other processes are really not efficient.

Did you ever thought about the downside of NOLOCK? Like dirty reads? Do you know what dirty reads are?

@gokaysatir I think for the use of NOLOCK. Please don't use NOLOCK because you really can read data which isn't really there. Only use it when you really are sure this is not a problem. Don't use it by default.

For answer to the question. I think this table design is fine.

Answer 2

If you want to create a view, you can use something like this:

SELECT *
FROM Users (NOLOCK)
INNER JOIN Events (NOLOCK) ON Users.Name = Events.Name

If the "Name" column in Events table, is not a connection to "Name" in Users table, you can not connect those tables.

You need a shared column in tables to be able to connect them inside a view.

Example: There may be UserID column in Events table, referring to the user who owns the event.

Yes, tables should have one shared column to be connected.

Example: A user with ID: 14 sent a login request. Events table SQL string should be like this:

INSERT INTO Events (UserID, EventID) 
VALUES(14, 1)

Also you need one more table according the example, which connects EventID's with the names, like this:

Table EventNames

EventID    Name
1           Login request.