I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up.

I am using Postgres 10.

Basically, I have a User table with unique user_id's. Each user can start a "Sleep" session, where we will record information about their sleep on our front-end. They can also have a "Focus" session. The user decides when these sessions end, and when they do I want to just delete the session information. Each session has columns of information to record (like a start time for example).

Also, a user cannot have a sleep session and a focus session at the same time in the database, there can only be zero or one session per user. Sessions are not shared and can only belong to one user.

I wasn't sure what the best database design was for this. Here were my thoughts:

1) have null-able columns in the user table for each session. This will create a lot of nulls if a user has no sessions, and still have lots of nulls because only half of those will be filled at one time at most.

2) have null-able columns in the user table and have a session_type column to distinguish the sessions. Big drawback is that the sessions hold slightly different information (although they share some columns), so there will be lots of nulls and maintenance and constraints get harder.

3) create a Session table with a type column for focus or sleep. Have columns not used for that specific session type as null. This seems hacky based on my in instincts and no better than solution 1, but I may be wrong.

4) create a Focus table and a Sleep table. Have unique foreign key constraint to user_id. Drawback is that I would have to make sure to not add a row if the other table already has that user_id, and making a query seems unreliable because of race conditions.

5) I did read about creating a "supertable", but I have no idea where to start with that if it is the most viable solution and am wondering if it's more trouble than it's worth, since it seems a little "over-engineered" for my specific purposes based on my incredibly minimal database experience.

Any guidance on how to think about these types of design choices is very much appreciated. Thank you in advance :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.