How to model state change timeline
I've got an app where users can place orders for recurring delivery of a particular service.
The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.
When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.
My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?
I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.
I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!
(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)
postgresql database-design relational-theory
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I've got an app where users can place orders for recurring delivery of a particular service.
The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.
When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.
My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?
I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.
I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!
(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)
postgresql database-design relational-theory
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I've got an app where users can place orders for recurring delivery of a particular service.
The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.
When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.
My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?
I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.
I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!
(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)
postgresql database-design relational-theory
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
I've got an app where users can place orders for recurring delivery of a particular service.
The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.
When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.
My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?
I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.
I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!
(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)
postgresql database-design relational-theory
postgresql database-design relational-theory
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
edited Jun 12 '15 at 16:05
Colin 't Hart
6,43882533
6,43882533
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
asked Jun 12 '15 at 13:36
AndrewAndrew
1063
1063
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
add a comment |
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
});
}
});
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%2f103899%2fhow-to-model-state-change-timeline%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
add a comment |
I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
add a comment |
I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
answered Jun 12 '15 at 14:24
Rui PachecoRui Pacheco
111
111
add a comment |
add a comment |
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.
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%2f103899%2fhow-to-model-state-change-timeline%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
