0

I'm honestly not even sure how to go about how to ask what I'm looking for/need. lol.

I'm currently using Sql Server for my database and the 'new' .net 5. There are multiple pricing tables that each have their own purpose for pricing lineitems on an order. These pricings get updated quite often, ranging from either monthly to once a year.

In the system they're currently using(super old), when a new pricing system comes out they create an entirely new table with those values, everytime. They do this because when an order is placed, it needs to get what the price would be on it's due date.

For example:

'orderA' is placed December 20th with a due date of December 30th. 'orderA' uses the current pricing data that's in effect.

'orderB' is placed on the same day, December 20th, but it has a due date of Jan 1st. 'orderB' would use the future data for pricing.

The example above is why they currently just create an entirely new table every time any pricing gets updated. I obviously do not want to do that, for a multitude of reasons.

I currently have functionality in place where users can schedule updates to different tables. This is basically just a stored procedure that creates a Sql Agent Job that gets triggered to execute an update query on a specific date.

Now this handles the ability to schedule updates, but it does not handle the ability to view if or what future data may be, which is the problem I'm running into.

I've looked into time-series data a bit, but I'm not entirely sure if that's the best course of action.

I'm also not entirely sure what kind of information can be pulled from a specific scheduled job. I feel that if I am able to get the jobs command, I'd need to essentially execute that query against current data without actually updating it to get the future data values or parse the query into a usable format I can use. Plus doing this, would probably cause big performance issues as it'd need to get ran multiple times for each line item in an order, depending on various pricing conditions.

So, what is the best course of action I should take? Whether it's something I have mentioned or there's something I haven't mentioned, please let me know.

3

You don't need scheduled jobs and new tables. Just have one table

Price
    ItemId    // cokecola
    DateFrom  // 2001-01-01 00:00:00
    DateTo    // 2010-01-01 00:00:00
    Price     // 150

Then select the price which is valid for your target date

select * from price
where itemId = @id
and DateFrom < @targetDate
and (
   DateTo is null
   or
   DateTo >= @targetDate
)

Now I've put two dates in there to make it easy to understand. But actually you can just use a single DateFrom and adjust your select to get the row with the min DateFrom which is still less than your target.

When you want to update prices, you just add new rows.

10
  • I did forget to edit in that I have considered just adding a timestamp column and forcing users to enter in a new row for every single pricing table/row. But since our pricing system is super convoluted(based on custom algorithms), I was really hoping to avoid that as that will be very time consuming for the end-user.
    – Travis
    Sep 9 at 19:32
  • why would they be forced to enter any more data than they are currently?
    – Ewan
    Sep 9 at 19:32
  • Because if I were to enforce a start/end date on all our pricing tables, that would mean forcing users to enter in a lot of duplicate data every month/bi-monthly/yearly/etc... Which is a lot of redundancy and horrible UX. All of which is dependent on the specific pricing table.
    – Travis
    Sep 9 at 19:36
  • what do they enter now?
    – Ewan
    Sep 9 at 19:40
  • there's no extra data here so you shoul dbe able to take whatever they enter now and write code to do the equivalent operation. ie "next month everything is 10% more" just insert into price (select the current prices *1.1, nextmonthsdate)
    – Ewan
    Sep 9 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.