Professional Web Applications Themes

Design issue with data points of given dates - MySQL

I have a database design problem which I'd like a few pointers on, please. I need to store a data point - it's the cost of something for a given client. This cost will normally remain static, but on occasion it might change. I'm thinking a few changes per year, no more. Updating a value as required in the client's record is easy, but I need to be able to find out what the value was on a given day. In other words I need to be able to store the history of changes to this value, and to be ...

  1. #1

    Default Design issue with data points of given dates

    I have a database design problem which I'd like a few pointers on, please.

    I need to store a data point - it's the cost of something for a given
    client. This cost will normally remain static, but on occasion it might
    change. I'm thinking a few changes per year, no more. Updating a value
    as required in the client's record is easy, but I need to be able to
    find out what the value was on a given day. In other words I need to be
    able to store the history of changes to this value, and to be able to
    query the value from that history.

    In my inexperience, the first (and only simple) design I've come up with
    is something like:

    client_id int
    cost int
    date_changed date

    and adding a record to that table when the cost changes. I can then
    query the table for the given client, ordered by date_changed
    descending, where the date_changed is less than (i.e. before) the given
    date, and take the top row from the result.

    Is this a reasonable way to solve the problem? I'm sure others must have
    solved similar problems in the past - is there a 'standard' solution?
    Derek Fountain Guest

  2. #2

    Default Re: Design issue with data points of given dates


    Derek Fountain wrote:
    > I have a database design problem which I'd like a few pointers on, please.
    >
    > I need to store a data point - it's the cost of something for a given
    > client. This cost will normally remain static, but on occasion it might
    > change. I'm thinking a few changes per year, no more. Updating a value
    > as required in the client's record is easy, but I need to be able to
    > find out what the value was on a given day. In other words I need to be
    > able to store the history of changes to this value, and to be able to
    > query the value from that history.
    >
    > In my inexperience, the first (and only simple) design I've come up with
    > is something like:
    >
    > client_id int
    > cost int
    > date_changed date
    >
    > and adding a record to that table when the cost changes. I can then
    > query the table for the given client, ordered by date_changed
    > descending, where the date_changed is less than (i.e. before) the given
    > date, and take the top row from the result.
    >
    > Is this a reasonable way to solve the problem? I'm sure others must have
    > solved similar problems in the past - is there a 'standard' solution?
    sounds like an excellent solution to me

    strawberry Guest

  3. #3

    Default Re: Design issue with data points of given dates

    strawberry wrote:
    >> Is this a reasonable way to solve the problem? I'm sure others must have
    >> solved similar problems in the past - is there a 'standard' solution?
    >
    > sounds like an excellent solution to me
    If you knew how inexperienced I am with DB design, you'd understand why
    I'm so chuffed to hear you say that. :o)

    But I'm still open to better ideas if there are any...
    Derek Fountain Guest

  4. #4

    Default Re: Design issue with data points of given dates


    Derek Fountain wrote:
    > strawberry wrote:
    > >> Is this a reasonable way to solve the problem? I'm sure others must have
    > >> solved similar problems in the past - is there a 'standard' solution?
    > >
    > > sounds like an excellent solution to me
    >
    > If you knew how inexperienced I am with DB design, you'd understand why
    > I'm so chuffed to hear you say that. :o)
    >
    > But I'm still open to better ideas if there are any...
    Yeah, and IF YOU KNEW how inexperienced I am you'd take my praise with
    a pinch of salt

    8-)

    strawberry Guest

  5. #5

    Default Re: Design issue with data points of given dates

    [email]nomailhursley.ibm.com[/email] says...
    >
    > I need to store a data point - it's the cost of something for a given
    > client. This cost will normally remain static, but on occasion it might
    > change. I'm thinking a few changes per year, no more. Updating a value
    > as required in the client's record is easy, but I need to be able to
    > find out what the value was on a given day. In other words I need to be
    > able to store the history of changes to this value, and to be able to
    > query the value from that history.
    >
    > In my inexperience, the first (and only simple) design I've come up with
    > is something like:
    >
    > client_id int
    > cost int
    > date_changed date
    >
    > and adding a record to that table when the cost changes. I can then
    > query the table for the given client, ordered by date_changed
    > descending, where the date_changed is less than (i.e. before) the given
    > date, and take the top row from the result.
    The other possibility is using the "slowly changing dimensions" model from
    data warehousing theory. This is a more complex design model and may not
    be warranted given the size/scope of your data.

    Redo your products ("somethings"?) table like:

    prod_rec_no prod_id start_date end_date price other_attributes
    1 1 1-jan-2004 31-jun-2004 10.00 blah
    2 1 1-jul-2004 31-jan-2005 11.00 blah
    3 1 1-feb-2005 31-jun-2006 11.00 other blah
    4 1 1-jul-2006 null 12.00 other blah
    5 2 1-jan-2005 mull 25.00 widget
    .....

    The null end date indicates the current values for the particular prod_id.

    Then in your transaction table, store the prod_rec_no rather than the
    prod_id, and you will always be able to find the attributes of that
    prod_id at the time of the transaction, without having to do date
    comparisons:

    client_id prod_rec_no prod_volume purchase_date
    99 1 5 11-feb-2004
    99 3 3 21-apr-2005
    99 5 2 21-apr-2005
    .....

    If you want to (for some reason) preserve your historical client record
    values, then do the same for your clients table and store client_rec_no
    rather than client_id in your transaction table.

    Geoff M
    Geoff Muldoon Guest

  6. #6

    Default Re: Design issue with data points of given dates

    > The other possibility is using the "slowly changing dimensions" model from
    > data warehousing theory. This is a more complex design model and may not
    > be warranted given the size/scope of your data.
    <snip>

    Something along those lines was the first method I came up with,
    although I didn't get the details pinned down quite right. I decided it
    was probably too complex for my needs and opens up the possibility of
    overlapping date ranges (when the data says there is more than one price
    at a given date) or no date range (and hence no price) for a given date.
    Of course, if I do right this wouldn't be a problem, but implementing
    something simple would make more sense for me at the moment.

    But thanks for the input; I had a feeling there would be some form of
    pattern or something that held a standard answer to the problem.
    Derek Fountain Guest

  7. #7

    Default Re: Design issue with data points of given dates

    > It's a good one. The only POSSIBLE improvement is one that would require
    > some application code (or a trigger, perhaps) to maintain and that would
    > be to add an "active" flag on the record to make finding current prices
    > much faster than a huge collection of MAX() functions to find the most
    > recent date.
    That's a decent idea, although the archival nature of the application
    means that the current price is no more likely to be searched for than
    any other date. I like the idea of a trigger though - I might have
    another use for that... Thanks! :o)
    Derek Fountain Guest

Similar Threads

  1. display data points but not data tips
    By ahatl123 in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 30th, 07:17 AM
  2. Drag Data Points
    By Andriy Drozdenko in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 29th, 11:39 AM
  3. CFCHART problem with too many data points
    By Bill Gordon in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 6th, 10:57 PM
  4. CFCHART not showing all data points
    By csw11235 in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 9th, 05:12 PM
  5. newbie: xml data and plotting points
    By Justin Koivisto in forum Macromedia Flash
    Replies: 4
    Last Post: August 13th, 05:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139