When to calculate a value versus when to store it in the database

For some values, it can be tricky to know whether it's better to calculate it on the fly or when to calculate it once and store in the database. We talk through a recent scenario we bumped into, and what factors to weigh when making this decision

Download our free Laravel tips ebook.

Creators & Guests

Host

Aaron Saray

Host

Joel Clermont

What is No Compromises?

Two seasoned salty programming veterans talk best practices based on years of working with Laravel SaaS teams.

Joel Clermont (00:01):
Welcome to No Compromises, a peek into the mind of two old web devs who have seen some things. This is Joel.

Aaron Saray (00:08):
And this is Aaron.

Joel Clermont (00:16):
Aaron, we've had a discussion recently on how to do something in our Laravel project and I just thought it'd be a good topic for the podcast. Because we like to, at least internally, have sort of rules why we do this versus that and it makes things consistent and it kind of takes away some mental burden of having to think it through each time. I'll set up the topic, kind of get everybody up to speed with what we've been discussing. And then we can talk through what our reasoning or our rule is as a result of this.

Aaron Saray (00:49):
I like it that you make it sound so that we have just lovely discussions and that wasn't an argument.

Joel Clermont (00:55):
No.

Aaron Saray (00:55):
It was an argument. No, it was a discussion.

Joel Clermont (01:00):
Yeah. I was going to say I didn't think it was an argument but maybe I'm the problem then. Okay, so the idea is if you have some calculated piece of data, and I'll give a tangible example in a second, but if you have some tangible or some calculated piece of data, do you, every time you need that data, calculate it in code? Or do you store the calculated value in a field in the database? Am I setting up that-?

Aaron Saray (01:30):
I think when you say calculated data for the specific avenue we're going to go down, we're going to say more like a calculated business label or rule. Because there are differences between calculated data, like what's the tax on this?

Joel Clermont (01:45):
Sure, yeah.

Aaron Saray (01:47):
Versus a calculated business rule, is this pending or not?

Joel Clermont (01:49):
Okay. Yeah, that leads in nicely to just a kind of one tangible example so everybody's on the same page as us. So let's say that you had a payment table, the payment transitions through different statuses. Initially it's requested, maybe the person who's being requested to make a payment refutes it. Of course the payment at some point gets paid and then there's a settling process where the payment is transferred from the middleman out to the person actually being paid. In our particular scenario, there's four states. It's not a one-way flow, things can kind of go back and forth between states. But that's the basic idea, so do you put a status field in the payment table or do you just get status attribute on the payment model where you look at other fields to determine what the status is?

Aaron Saray (02:43):
Well, you might look at other fields, or on the same model, or other things in the application. I mean, it doesn't really matter where that data comes from, it could be anywhere. But is it calculated at the time of retrieval or is it stored in a database? I guess there's a couple of reasons why you would do both. First of all, think about when you do like the calculated reason. It could be that maybe that's not checked very often or that rule is changing or...

Joel Clermont (03:16):
I can think of another reason. Maybe you only start out with two states and you're like, "Oh, well this is simple," and then all of a sudden you have four.

Aaron Saray (03:24):
That's probably a better example. Because I was like, you know, I'm going to argue for the other reason but I'm having a hard time coming up with things now. Well, before we get into the reasons for putting it in a column, I'll talk about how this kind of bit me. We were working on displaying this information, something like a Nova, for example. And if you want to display it, well, we didn't have to have another piece of data because we had all the data we needed in order to give them a business related thing. Like, if there was a refuted explanation or if there was a stripe transaction ID or whatever, we knew what they meant in data. But the business wouldn't really understand that so we were we able to calculate at the time of viewing what the status is or what's the state of this payment is for the business users. And that was fine and great inside of Nova until we wanted to maybe build a filter or we wanted to do authorization on some of our API endpoints and we had to start retrieving information and seeing if it belonged to certain users and whatnot.
Now, there's two ways to obviously do this. You could retrieve everything about the model or you could retrieve everything that would get the proper model, retrieve that into a model and then check that attribute, like calculated attribute. Or, you could just do an exist query where you would filter on, like, if the state is this, then it exists. Good enough, I don't need to retrieve the whole model and hydrate it and whatnot. I'm just doing authorization check. Since it was calculated though, we really couldn't check that so this logic ended up being duplicating the status calculation in these little queries and that became a problem.

Joel Clermont (05:12):
Yeah, for sure. I felt the pain too and I was the one who originally created it as a calculated attribute on the model. And it's like, "Ah, this is...� So the more places you use it or the more different statuses you have, or the more complexity there is in calculating it, all of these things are where you start to feel the pain.

Aaron Saray (05:30):
Yeah. The more complex calculating it, the more times you have to calculate it, that more expensive that is. It's not hard at first when you're just checking to see if certain properties exist, but if it is like a sort of situation where you have to check other models and maybe even a third party, well, then it can be a problem. Then, of course, the other thing is when you want a query on that or filter on that, especially in something like Nova too, you really can't easily do that.

Joel Clermont (05:56):
Well, and just one more place that bumped into us. We had some queries set up and query scopes to do things like in the controller to filter results, but then we also wanted to expose this as a field in the resource that's being returned. And it's like, well, you can't really use that query scope. Like, I essentially had to duplicate the logic and at that point we knew this was definitely the wrong decision and then you had to unwind it. But, yeah, there's all these different places where it came up.

Aaron Saray (06:27):
Well, when you say we, you mean you knew. I knew on the last poll request. "Dang it, Joel. Like, this is so wrong."

Joel Clermont (06:34):
Well, it wasn't until I personally felt the pain, Aaron, that I decided it was something that had to be corrected.

Aaron Saray (06:41):
There is a opposite side of this to consider though. Which is, if you are developing a sort of state or status of something, does that state or status change for existing objects if the business rules change?

Joel Clermont (06:56):
Oh, okay.

Aaron Saray (06:56):
I'll use a good example. Let's just say its user onboarding, right? In January the onboarding is I need the name, email address, I need them to verify their email, I need them to enter in three pieces of demographic information. And then when that's finished, they're on boarded. You could track that they're on boarded when they add in another screen where they add in the rest information. Or, you could have a calculated attribute that says, "They're on boarded if these pieces of information are not empty." The question then becomes more of almost a business related question, which is, let's just say we add on another screen for onboarding two more pieces of information, are all the existing users now not on boarded? Because if you use the calculated thing, they're going to be not on boarded unless you do some sort of date magic and all that. It's just going to be-

Joel Clermont (07:48):
I can't imagine you ever adding like three more nested if's with hard coded dates in there. Just that would never happen.

Aaron Saray (07:54):
Right. So that might be a reason why you have an on boarded field. Because, yes, at the time of the onboarding process that they completed, they were on boarded, now you need new information. But if it's something a little less final as on boarded, you might have like, "Is this profile complete?" You might then use a calculated field because you might uncomplete people's profiles a little bit for showing on like a dashboard and saying, "Hey, you have more to fill in." So that, "Is profile complete?" maybe wouldn't be shown to them but it would be a field that you check to see then what is the next step that they're required to do.

Joel Clermont (08:32):
Yeah. All right, that's another good place where this decision comes up and what might influence what your decision is.

Aaron Saray (08:39):
I think the final thing to talk about then too is, how would you keep this field up to date? It kind of, again, talks about those sort of events and observers and all those different things that we talked about in a previous episode. I struggle with the exact way to apply this because it really just depends, again, on what the field's context is that you're updating. So if it's based off information maybe it's an observer, but if it's based off user's actions maybe it's issued from an event and updated in controllers and stuff. Because there have been situations where I've built something where someone who updated the model in an admin screen didn't necessarily trigger the event because an admin, while they were filling out the data in the model and they're authoritative, they didn't take the action. So you would hate for the user to get a message saying like, "You've updated your email." And they're like, "No, I didn't," so there's a difference there. I mean, that's something you have to consider on each particular... or on each scenario.
I was watching Jay Leno car show the other day and one of the segments they have is, this is your automotive life. And they put people in front of a garage door and then they beep the car horn and then they start the car and the person has to guess which car out of their history that was.

Joel Clermont (10:08):
Okay, all right.

Aaron Saray (10:09):
And some people can identify it from the car horn. Which I think about it, I had a 78 Impala that I think if I heard that horn I would definitely recognize that. But what I find interesting is, why aren't there more pitches to car horns? I mean, they pretty much all sound the same.

Joel Clermont (10:30):
Actually, I was just thinking. I think it's a Tesla thing, you can use any MP3 as your horn.

Aaron Saray (10:38):
Oh, I thought they stopped that.

Joel Clermont (10:38):
Oh, did they stop?

Aaron Saray (10:41):
Because people were playing not good things in crowds.

Joel Clermont (10:46):
I can't imagine somebody doing that. But, no, that's a good question, Aaron. I don't know.

Aaron Saray (10:52):
I don't mean like playing a whole song or anything like that, but you know all horns are like. Like, why isn't there a different sound? Well, you know what's really bad is the backing up sound. You know, "Beep, beep, beep." There are some studies done that say that those higher pitch loud noises, it's really hard to actually detect what direction they're coming from for people, I guess. This entire time we've been doing it wrong so now the newer trucks have this white noise, which sounds like some sort of cat dying. So when they back up it's like, "Ah, ah, ah."

Joel Clermont (11:31):
Yeah, the Amazon trucks in our neighborhood have that. And every time I hear it I'm like, "What is that?"

Aaron Saray (11:38):
What's dying?

Joel Clermont (11:39):
Yeah. Well, I think you just solved a mystery for me, Aaron. Because the high pitch thing and not being able to detect the direction it's coming from. There was some crew working in our neighborhood and they had the backup sound going for like 30 minutes at a time and I even stepped outside my house. I'm like, "Where is this coming from?" I took a lap around our whole neighborhood, I couldn't identify it. It ended up being our neighbor, but it was like a crane or something in their backyard because they were doing work on the roof. But, wow, that drives you nuts. I could hear it inside the house, but no idea where it was coming from.

Aaron Saray (12:13):
You're such an old man. "Oh, I heard a sound and I took a lap around the neighborhood just to see."

Joel Clermont (12:21):
I was going to take a walk anyways but then I had a secondary mission.

Aaron Saray (12:25):
You're looking for a little bit of more knowledge drop from Joel and Aaron. Well, we can help you out with that.

Joel Clermont (12:30):
We put together a free eBook with many more tips that are little bite-sized chunks of information. Go to masteringlaravel.io and download the free tips eBook today.