I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables
user_id (pk) user_name user_dob ...
session_id (pk) session_date session_duration ...
user_id (fk) session_id (fk)
As you can guess one user can have many sessions, the reason (I believe) the join table was used is because a session can be added before the user exists (and it is linked to the user after they signup), thus avoiding a nullable field in the sessions table...fine.
But I have recently been looking at creating some multi-column indexes to speed up certain queries and have realised that I cannot include the user in this index as the column is in a different table.
As I am fairly new to DB design I am just wondering if the above design is correct? Or am I in fact better off using a nullable FK in the session table to allow me to index the user relationship?