Click here to Skip to main content
15,358,692 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 7-Apr-22 23:04pm
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

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
   
Comments
Gianluca Locri 8-Apr-22 5:10am
   
This seems like a good compromise. I think I will go with this one!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900