Database Design to Store Events Which Have Different Required Attributes
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.
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.
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?
database-design
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
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.
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?
database-design
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
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.
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?
database-design
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
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.
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?
database-design
database-design
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 1 min ago
Eric
asked 21 mins ago
EricEric
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 21 mins ago
EricEric
1
asked 21 mins ago
EricEric
1
1
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Eric is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Eric is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226780%2fdatabase-design-to-store-events-which-have-different-required-attributes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Eric is a new contributor. Be nice, and check out our Code of Conduct.
Eric is a new contributor. Be nice, and check out our Code of Conduct.
Eric is a new contributor. Be nice, and check out our Code of Conduct.
Eric is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226780%2fdatabase-design-to-store-events-which-have-different-required-attributes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
