Building a little app which allows the user to create time based line charts. For example, a chart with a 1 month x-axis time duration and with series for both revenues and profits the current year as well as last year.
My intentions is to store each individual chart with its time duration (1 month) in charts and each series and its offset (revenues with 0 offset, revenues with 1 year offset, profits with 0 offset, profit with 1 year offset) in series.
A business rule (which hopefully will never change. Please assume it will not) is duration and offset can only be integers with units of hours, days, weeks, months, quarters, and years. The UX requires that the user's originally selected duration and offsets along with their units must available so they can view the charts configuration, however, they can later change the value and units of the duration and offsets.
How can/should both a value and the value's units be stored in SQL? The following will work, but I don't know whether there is a more appropriate means to do so.
Have you experimented with charting? I think you will find that dynamically building a chart is "fast enough". That is, the storing of canned charts is "premature optimization" (and complexity) that should be abandoned.