2 ms·

However, that requires doing an extra lookup for every write on the most active / real time table. It would be better to add an optional product_id to the accou

by sgc 4y ago

However, that requires doing an extra lookup for every write on the most active / real time table. It would be better to add an optional product_id to the account table, which then allows to have different rates for different products at the same time for each account. You really don't need a tier table, if you are always using multipliers to the base rate, and we could eliminate it unless we want to store tier types as well, that allow for other formulas beyond multipliers. The account, tier, and product tables already take care of changing prices using timestamps, but I can see that you might offer a fixed rate to an account that spans a price change on the base rate. There is an entire facet to this which is not really about the db, but business logic - like whether we are going to change rates used for calculation based on time of use, time of account creation, time of rate tier activation, product base rate change - change that logic if it's a promo rate - etc. I am not totally sure that we need to go beyond what we have for the dbs to work with this business logic, it is better handled and enforced by the calculation classes. I don't think calculations are ever that hard, because I presume you will have previously written and optimized lookups and calculations for all common to semi-common cases. I would also (obviously?) never calculate on the live server, but dump the db and calculate billing on an admin server. If the site is busy enough, I would have this entire db on an admin server, and just import the usage logging table from a simpler db on live. For live, you just need enough account and product info for authentication and logging.

withinboredom 4y ago
> However, that requires doing an extra lookup for every write on the most active / real time table. You only have to do that lookup once and you're likely looking up other things to write that event as well. So it is almost a free cost. You can actually do all of this async and faster: Just call a url containing all the details in the url (/usage?user_id=123&product_id=123&type=start_usage) that returns a 200 response with a minimal payload (0 byte static file). From there, just stream the server's access logs to Apache Kafka after the logs rotate once per minute to be parsed. There it will do all the lookups and aggregation. You're probably only looking at a delay of 4-5 minutes before the data is available to the end user -- even with millions of active users. Then you can parse the logs async as fast/slow as you need to.

by sgc 4y ago

withinboredom 4y ago

> However, that requires doing an extra lookup for every write on the most active / real time table. You only have to do that lookup once and you're likely looking up other things to write that event as well. So it is almost a free cost. You can actually do all of this async and faster: Just call a url containing all the details in the url (/usage?user_id=123&product_id=123&type=start_usage) that returns a 200 response with a minimal payload (0 byte static file). From there, just stream the server's access logs to Apache Kafka after the logs rotate once per minute to be parsed. There it will do all the lookups and aggregation. You're probably only looking at a delay of 4-5 minutes before the data is available to the end user -- even with millions of active users. Then you can parse the logs async as fast/slow as you need to.