Persisting business logic params in the DB
Is there any best practice on how to save rules in a database? For example i would like to save the rules on how to calculate the profit margin of a window. The default profit would be 50% . If it is big dimensions them it would be 70%. This is a simple case but how could i persist this in a DB? I would like to be able to swap this rules when doing quote estimates since i need to respect the prices of old quotes when they just need slight modifications.
10 Replies
I have two ideas. First suggestion would be to:
1. Have a table of parameters in the DB, with a document of default parameters. When you want to update the parameters, you add a new document. When you want to apply parameters, you grab the latest document, and store its ID with the quote, so you know what rules you applied with it.
2. Put the parameters in code, but when you save quotes, save the parameters at that time. So in the future you'll have a record of what they were.
Sounds good
I will also have to save the pricing of each raw material. And save the pricing with a version and timestamp everytime there are pricing changes from the vendors. This way i can apply time travel of pricing for the same reasons of respecting pricing to older quotes
So each quote will save a reference to a pricing version
gotcha. yeah, storing the pricing in an append-only table, and storing the price's ID seems helpful there. it just means looking up pricing requires fetching the latest row
Or, go the "denormalized" route, by storing the price and version in a single document that you update. When you use a price, you copy the price & version into the invoice doc. You don't have a centralized list of prices in this case, but there's a simpler DB setup and not as much DB lookups.
I’m planning on saving the computed prices of the final product in the quote. And an ID of the raw material pricing version. Then index each raw material pricing by version in case i need to recompute i can fetch old pricing and recompute
Gotcha. Yup that sounds good. The denormalized route would be to store each raw material price in the quote as well. Obv. this assumes you want to use the fixed prices and not the latest prices
But then i guess it would be convenient to have another table with the meta data of each pricing version. Like a timestamp and status (draft or published)
This is hard because there are hundreds of parts
And variations
The other way would be to have the itemized pricing nested on the pricing version table. 🤔
But may not be ideal because it could surpass the document size limit
if you store the prices in a nested object, it can have up to 1024 fields. The total size of a document has to be under 1MB, but it might be large enough to store all the prices on the quota, or in a pricing table as one object
Another way of storing a price history (if the price doesn't change often), is to store it on each part. e.g. a part table could look like:
True. Could i store it as a Map?
this is an existing app but with this new feature of pricing versioning. So I’ll start by using convex for the pricing versioning while incrementally migrating from fauna
yes, you can store it as a map too. If it's a map, though, I'm not sure you could do a query like
q => q.neq(q.field('oldPrices.v1'), null) to look for old prices of a certain version. Your schema would be more complicated though: you'd either define all the price names, or have to define it as s.any().
Map is more correct, so I'd go with that if you canI see, thanks 👍