I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables
users
user_id (pk)
user_name
user_dob …
sessions
session_id (pk)
session_date
session_duration …
user_sessions
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?
Best Answer
If a session can only belong to a single user at any given time, the more appropriate model would be without a join table (
user_sessions
). What's the problem with columnuser_id
beingNULL
?If you need it to be
NOT NULL
and you don't always know the user at creation time, just insert a user "unknown" (withuser_id 0
in my example). Can serve asDEFAULT
value, too ...Note on indexing strategy
Postgres can combine multiple indices with excellent speed. Typically, in your original scenario, with a nested loop:
Say, you have these indices:
Both should be covered by the pk constraints in your original model automatically. However, the second one could go either way
(user_id, session_id)
or(session_id, user_id)
and that matters:Is a composite index also good for queries on the first field?
For best performance in a query like:
You would need the index (or pk constraint) to be
Or have an additional index with
user_id
as first column.