This database also has a session monitor that logs every access to the database, with a relation to the calendar row id. This way, I can make database access reports, without replicating the date value.
My question is: for a membership table should I follow the same principle and relate member row to the session monitor, which in turn, relates to the calendar or should I put the date just there?
Some of the tables of this database will have to handle some heavy load, both for updating and selecting. This said, my question is should I make a link or put the date just there to extinguish the need to make 2 joins just to know when something was registered / updated? If I only place the relation to know the date I'll have to do something like:
DATEADD(ss, x.timeOffsetInSeconds, c.dateValue) AS date
JOIN sessionMonitor sm ON sm.id = x.sessionMonitorId
JOIN calendar c ON c.id = sm.calendarId
Instead of just doing a select x.lastUpdateDate
How would you gurus usually deal with these situations?
The id column is there only becaused I assumed that - from a performance point of view - it would be better to create a relation between two ids and not a date value, for the more unique it can be.
If I stick to the idea of relating every date in every table to the calendar table it would make sense (in my mind) to be relating IDs and not values.
Still, unless the underlying answer in your reply is that if I link values I won't have to do the joins, which is true, I still have the question of how to approach this? link everything to the calendar table or just put the date value in every row that is inserted / updated.