Best practices: database table relationships based on timestamps

Question

0.00/5 (No votes)

See more:

, +

I have a question is about good database design.
In my current project I have a SQL database with two tables.

A sessions table containing:
- session id: the primary key
- login timestamp: the unix timestamp of the start of the session
- logout timestamp: the unix timestamp of the end of the session
- user id of the logged user

An events table containing:
- event id: the primary key
- event timestamp: the unix timestamp of the event
- action id: the type of event occurred

An event may occurs during a user's session, or it may occur while no one is logged in.
In the described scenario only one user can be logged at a time.

I need to get the list of events occurred during each user session so I need a way to link each event to a session, whether the event occurred during a session.

What I have tried:

As a first solution I thought of including a nullable foreign key in the events table referring to a session.
In this case, if an event occurs without a user being logged in the FK key will be NULL, otherwise it will contain the session id.

But with this solution there is a data consistency problem: it would be possible to create an event linked to a session and give it a timestamp that is not between the start and end timestamps of the associated session.
This is not desired since the event may relate to a session having a time window that does not include the event time itself.

A second solution would be not to use the FK described and to retrieve the data by means of a query that takes into account the start and end timestamps of each session and returns for each event the id of the session to which the event belongs.

The final question is whether the choice of not using an FK makes sense for this scenario or violates the principles of good database design, and if such a technique can lead to performance problems in data retrieval since the conceptual relation between sessions and events don't rely on FKs.
Or in other words: I wonder whether in an ERM it is always necessary (or desirable) to find a relation that uses FKs to associate conceptually related data.

Final Note:
One coolegue suggest me another solution: consider login and logout as events. This is not feasible because this solution can lead to even worse results.
Infact I would need to link events to users but then I could insert en event sequence like login_event(linked to user_1) - foo_event(linked to nobody) - logout_event(linked to user_2).
This seems messy to me...

Posted 7-Apr-22 21:14pm

Gianluca Locri

Updated 7-Apr-22 23:04pm

Add a Solution

Comments

Richard Deeming 8-Apr-22 4:04am

"only one user can be logged at a time"

That seems like an odd and artificial restriction. And even if it applies today, are you sure it will still apply in six month's time?

An FK relationship seems like the best option to me. That way, you should be able to allow for multiple simultaneous sessions in the future.

Gianluca Locri 8-Apr-22 4:45am

No, I think this is not a restriction in my case. I've omitted some details in order to get to the core of the problem.
Think about a kiosk-like machine. Here only one user at a time can log in to the machine and performs some action.
Sessions are also linked to the machine_id as well.
So the database is unique for all the machines.
And yes, some events can occour also without a user being logged in and I need to track them as well (think about some action performed by some guest user, to put it simply).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2022-04-07T23:04:00

I'm with @Richard-Deeming - the nullable foreign key seems like the best option

Quote:
it would be possible to create an event linked to a session and give it a timestamp that is not between the start and end timestamps of the associated session.

That is a matter for how you are logging the event and linking it to a session - appropriate validation should avoid that scenario i.e. before linking the event to a session check for the id of the currently logged in session.

Simple example:

SQL

declare @sessions table (sessionId int identity(1,1), [login] datetime, [logout] datetime, userId int);
declare @events table (eventId int identity(1,1), eventTime datetime, actionId int, sessionId int null);

insert into @sessions([login], [logout], userId) values 
	('2022-04-08 09:00:00','2022-04-08 10:00:00',1)

declare @dt1 datetime = '2022-04-08 09:30:00'	-- i.e. during session 1
declare @dt2 datetime = '2022-04-08 11:30:00'	-- i.e. outside session 1

declare @sesh int = (select sessionId from @sessions where @dt1 between [login] and [logout]);
insert into @events (eventTime, actionId, sessionId) values
(@dt1, 100, @sesh) -- expected value for sessionId = 1

set @sesh = (select sessionId from @sessions where @dt2 between [login] and [logout]);
insert into @events (eventTime, actionId, sessionId) values
(@dt2, 100, @sesh) -- expected value for sessionId = null

select * from @events

results

eventId	eventTime				actionId	sessionId
1		2022-04-08 09:30:00.000	100			1
2		2022-04-08 11:30:00.000	100			NULL