I am building a billing database for my office. I am planning to write a set of queries to determine time overlaps because my users get billings per minute. In order to do this each user will have a set of "events" that they will enter into the database. My question is about how best to setup the "events" data since the events may have different required fields. The simple events are
Start - User + Date/time + ClientID/CaseID
Stop - User + Date/Time + ClientID/CaseID
Then things become a bit more complicated. The Users may also "handoff" to another user, and we also have times when a user is supervising an assistant (assistant names/ids/etc will be in another table since they are not users). Basically a set of all possible events....
Start w/ Supervision Start Supervising After Start Take Over from Supervising Hand-off from One User to Another (while supervising or not)
When I do my billings later, I need to query a few things:
1) All periods of time when a user is billing and not supervising.
2) All periods of time when a user is supervising 1 or more cases, including finding gaps between where the gap was greater than 30 minutes (e.g. a user may supervise up to 4 assistants, and I need to find the intervals where they were supervising 1 to 4 rooms, but also any gaps between supervising)
3) Ensuring that a user can either be not supervising running 1 room, or supervising from 1 to 4 rooms (but not running a room and supervising at the same time).
So I thought about a few was to do it.
1) Table per "type". So a table for Start/Stop, a table for HandOffs, a Table for Supervision, etc. This would work, but seems like a lot of tables. I could even extend this to have a starts, stops, handoff, start supervision, stop supervision, etc....
2) Single "event" table with an enum for the event type and then a set of columns, two user columns, two time columns, two assistant columns. user_a, user_b, assistant_a, assistant_b, time. Since theoretically I can determine the type by which variables are present... this just seems like a lot of logic to build. (e.g. determine the type based on the permutation of which columns are non_null)
3) JSON/JSONB column type where I have a enum for the type and then use a json object with keys for the different variables.
4) Postgres also has "inheritance"
I am trying to figure out the best way to store this data so that I can also query the things above. Thoughts? Or am I overthinking this?