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:
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'
declare @dt2 datetime = '2022-04-08 11:30:00'
declare @sesh int = (select sessionId from @sessions where @dt1 between [login] and [logout]);
insert into @events (eventTime, actionId, sessionId) values
(@dt1, 100, @sesh)
set @sesh = (select sessionId from @sessions where @dt2 between [login] and [logout]);
insert into @events (eventTime, actionId, sessionId) values
(@dt2, 100, @sesh)
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