Welcome to No Compromises. A peek into the mind of two old web devs who have seen some things. This is Joel.

And this is Aaron.

I am commandeering this podcast for my own personal aims today, Aaron. And you're just going to be along for the ride here. Is that okay?

Yep.

Anyways, I have a technical question. Something where I see two clear ways I could model some data and a relationship, and I see the pros and cons of both. And I want to pick the one that's going to get through code review with you before I go off and do this.

So I want to bring you into the discussion here. Does that sound good? Okay.

Yep.

All right, here's the situation. We have a model, let's just call it check, like we're checking something. And it can check a number of things, so let's just call those items. And there are certain... a check has a type, and items also have types, and they have to line up.

If it's a check of type A, I can only check items of type A. Are you with me so far?

Yep.

Okay. Now, here's the thing. These items can have more than one type. So right away I think, "Oh, it's big deal." It's like a one-to-many relationship. A check can only have one type, an item can have more than one type.

Making this even more confusing in my mind, the types are not models. They're enum values, right? In a typical one-to-many relationship, the many is like another model in your database. And then you get foreign key constraints and you get all that good stuff. Are you seeing where I'm going with this?

Yep.

Okay. The two options I'm thinking of... And I'll just present them in no particular order and then I want to hear which one you would choose and why. Still use a one-to-many relationship and then use a pivot model where what would be the foreign key is actually just an enum and we can cast it as such.

But then we can use all the normal, one-to-many, and belongs, all the normal Eloquent relationships. Or, I just put another column on this item table, the one that can have more than one type. And it's just a JSON column and I can even cast that to a collection of enums, like Eloquent supports that. What are your thoughts?

I don't think I understand the problem yet.

Okay, fair enough. I know I'm dropping you in cold, you have no prep on this.

So, we have a check and it has one-to-many items, and those items have to match the same type as the check.

Correct.

And then each one of those items has one-to-many types it can be, but those types are logical business-related enums.

Yes. It would be a string value stored in the database, not a foreign key ID to that other table.

So what is the actual problem though? To me, this sounds like a validation filtering thing. Like, if you're trying to assign something, you want to filter it on a type or a value.

It'd be like the same thing as I know there's many different checks, but if they belong to a different company versus another, well, there's a value on check. Probably a check ID or a company ID or something so I'm a little confused, yeah.

Okay. In this particular case, I think you're getting ahead of me a little bit. There could potentially be some validation. In this case, this is like data receding, at least at this stage of the project, it can't be edited on the fly. Like, all these relationships between the types and everything.

But that could be coming down the road. In fact, it probably will be. And we will use it for filtering so when I am putting together a check, I should only see items I get to add that are of that matching type. It'll be like a very basic filter.

You know, I picked the checks type, now my item list is filtered to matching types. But before I even get there... I mean, that is important context, but how would you structure the data? I guess that's the problem I'm thinking of. And the two ways to do it is a pivot table or adjacent column.

Well, that's the two ways you thought of.

Right, there might be a third. That'd be even better.

Right. So, I'm trying to understand the use case because I think it's important to understand the use case before we talk about structuring the data.

Let me give you a little more information because you're right. I'm just trying to get you to give me an answer because I want to go build this and you're like, "No, Joel, I have questions." The list of items is relatively small, it's like a couple hundred.

On top of that... so it's not like a million records. Like, if you're thinking of, oh, performance for filtering and querying. Really small table. Of those 200-plus items, literally only two have more than one type currently. I don't know what future data will bring.

But that's an indicator to me like maybe there's a third option, which is like, don't do a one-to-many, just have two of those items, each with a different type. I just wanted to throw that context in there.

Okay. I am still understanding that a check has many-to items. I know that you'll have to do a filtering if you are creating some sort of CRUD to add items to a check.

To me, that sounds like it has to be filtered on a type column or a type JSON thing, like you had talked about. I don't remember what the other suggestion was, but it didn't make any sense to me.

The pivot table?

Yeah, that doesn't make a lot of sense to me because I think that'd be really confusing. Because you'd have to... I don't even know how you would do it. Yeah, I don't like that idea. That one is way too complicated.

I can't actually build it, but I know how I would build it. To me, that would only really make sense if there were many, many records. Like, we're filtering by JSON, which is relatively efficient in MySQL now.

If I was concerned about that, like, "Oh, I can't make an index to cover this appropriately." But I think I see where you're leaning, so yeah.

So it's really just about... But I'm going back to the thing, it's about filtering. That's all you're asking. Is like, "How do I filter items down to a smaller subset so I know which ones I can actually attach to this?"

Yeah, that's the end result. But I'm introducing this concept of items having types, in the current system, they don't have that. Items are just like items and there's no filtering currently.

I'm trying to import the data and then I'm like, "Oh, how should I structure this data?" But yes, the eventual end result is I will want to filter based on this new data that I'm creating.

To me, it seems like your second option with the JSON pipe sort of thing, and you can query off of that. Like you said, I don't know much about the indexes and stuff, but if it's small, it should be fine.

It's not something we're even using that often. Based off your use case, it sounds like once they're attached you don't have to filter by those types again because it is the same type. It's only like a very small case, which someone's doing some sort of CRUD on this.

Okay, I feel good about that. That is where I was leaning, by the way. At first, I'm like, "Ah, Joel, don't be lazy." Because at first I'm like, "Oh, just create a column and I'll put an array in there." And I'm like, "Am I being lazy? Am I being weird?"

And I'm like, "This should be a relationship because it feels like a relationship." But then I had the light bulb, it's like, well, there's no model on the other side of the relationship so it isn't really an Eloquent relationship. It almost felt forced if I would've tried to model it that way.

Anyways, this was very helpful and I just want to throw one more detail in here because I rubber ducked this with a cloud code. And it actually was all over the place, it was not as helpful as you were. I think you're probably glad to hear that.

But at the end, I was leaning on the JSON column. It says, "I think you're right, let's pivot to that," and it said, pun intended. I'm like, "Nope," and I closed it.

No.

To make it a little bit more valuable than just for me. I'm kind of taking a step back and sort of thinking about this conversation. And one thing that stood out to me, and you called me out on maybe twice, was I was just trying to jump to the solution.

Like, this is such a developer thing to do and I think part of the problem is the problem was fully formed in my head and I wasn't giving you all of that context and I was asking you to make a decision for me based on limited information. I'm glad you pushed back on that.

Yeah, that makes perfect sense. What I really wanted to do is like, "Hey, what is the problem? What have you thought through? And then what are your solutions?" It's almost like you're just talking with anyone.

You're talking with another developer, you're talking with a friend, you're talking with an AI tool, any of those things. It's like you need to give it all that context and then be like, "Okay, well, this is my thoughts. Now help me go through that." Because you're right, I was like, "What's the point of this question?" No, the answer is don't do anything and in your quarter, you will pass.

I like that. I want to share something funny that my son said to me, but it will only make sense if I tell you other stories to set it up. First of all, Aaron, you know-

Okay grandpa, tell me all your other stories.

Yeah, back in my day. No, I don't know how long ago this was. It was at least four or five years ago, but there was an incident where there was a boom outside of our house. And for whatever reason I decided to go on Twitter.

And I'm like, "Hey, did anybody else in such and such town hear this?" And people replied and they're like, "I did hear it." But what's funny is like one of the local news TV stations picked it up, came to my house the next day, and interviewed me.

Okay, that's already a little weird. But the best thing of all and to this day I will laugh about, the caption. When they showed me and they're interviewing, you know, Joel Clermont, the caption said, "Heard loud noise". So just completely stupid.

Okay.

My kids make fun of me. It's great, whatever. All right, that's peace one to set up what I'm about to tell you. A couple weeks ago I was waking up, the power went out at our house, and the electric pole outside... not real close to our house, but maybe a hundred yards away, was on fire.

So something was wrong in the grid and then it blew up and it made a loud noise. Anyways, the kids wake up and I'm telling them what happened and my dad's like, "Hey, Dad, maybe the news will call you as they're loud noise correspondent."

So this guy always hears loud noises.

Yeah, so I'm putting that on my business card. I just want you to know that.

Yeah, I think so, loud noise corresponded. Joel and I don't just push at each other and go in depth in our conversations, we actually dig really deep on code reviews too.

And we could do that for your team, that's a service we offer. If you'd like us to take a look at your application and do an architecture or code review, head over to masteringlaravel.io and click on contact us.