4 ms·
> The tricky part of this proposal is that I’ve encountered many data modellers in my career who can offer the “denormalized” option, but few … nay, none who co
by lukeasrodgers 3y ago
> The tricky part of this proposal is that I’ve encountered many data modellers in my career who can offer the “denormalized” option, but few … nay, none who could tell me what the properly normalized data model was.
We have a data model like User has many Xs and Ys, both Xs and Ys have a foreign key to the User via user_id. The normalized version would be User has many Ys through Xs. We denormalize and store user_id on both for performance reasons. This seems like a) so common, b) so straightforward a refutation of this claim that I must be misunderstanding the author.
- housecarpenter 3y agoCould you explain your comment further? I don't understand it. Specifically, I don't understand what "has many Ys through Xs" means. That phrase just doesn't syntactically make sense to me. And then you talk about storing "user_id on both" as a denormalized solution---but what that makes me think of is having two independent one-to-many relations (user-to-X and user-to-Y), with a table for each, each having a user_id column linking back to the user---but that's perfectly well normalized as far as I know, so I assume you probably intend to convey something different.
- marcosdumay 3y agoIf I understood it correctly, the GP is talking about those normalized tables: U | X X | Y and claiming that if you need the relations of U | Y often, you are better storing them as: U | X U | X | Y Or just the later table, since the first is now completely redundant. So yes, that happens, and depending on the size of those tables, it may be true that the denormalized for is faster. But it's just wrong to claim that one or the other is faster without any further information, and IMO, the more common case is that the normalized one is faster.