Relationships: Start with Several(lukebechtel.com)
lukebechtel.com
Relationships: Start with Several
https://lukebechtel.com/blog/relationships-start-with-several
34 comments
Can second this, it’s premature optimization. The real problem is usually having no easy and reliable way to migrate data / apply schema changes.
I've had a pretty easy time transitioning from one-to-one to one-to-many by just keeping the old 1-1 mapping, renaming it as the "primary" option, and then adding a list of secondary options. All of the old functionality continues to work, and any new stuff that needs multiples can operate on "[primary] + secondaries".
> When users are only doing one-to-one right now, that's what you're going to be designing the UI for. Nobody likes a pointless dropdown to select the one possible option, after all.
Your UI does not necessarily have to expose the full flexibility of your underlying data structures.
Your UI does not necessarily have to expose the full flexibility of your underlying data structures.
Those who do not learn from relational databases are doomed to migrate to them.
Have an owners table, a pets table, and an owns table. Right from the start. It's the shape of the data, model it correctly from the beginning. Two people can own one cat. One person can own two cats. Model this relationship in the store.
Are there rare cases where a one-to-n rather than m-to-n relationship obtains? Maybe, but then again, you should assume you're wrong about that and interrogate the subject very carefully before deciding that one-to-one or one-to-n is correct.
The thing is that those cases can be modeled as m-to-n where one or both happen to be 1. Unless some error would arise from not preserving the singleton invariant, don't bake it in.
Have an owners table, a pets table, and an owns table. Right from the start. It's the shape of the data, model it correctly from the beginning. Two people can own one cat. One person can own two cats. Model this relationship in the store.
Are there rare cases where a one-to-n rather than m-to-n relationship obtains? Maybe, but then again, you should assume you're wrong about that and interrogate the subject very carefully before deciding that one-to-one or one-to-n is correct.
The thing is that those cases can be modeled as m-to-n where one or both happen to be 1. Unless some error would arise from not preserving the singleton invariant, don't bake it in.
This seems to go against YAGNI, and I'm not sure I agree it's good advice.
A system I worked on recently modeled users as potentially belonging to more than one organization, even though there wasn't a good use case for that and there wasn't any plan for how it should work if implemented. It made all of the code to get the organization from a user more complex than it should have been.
Another system I worked on had a view hierarchy that allowed a view to have more than one parent, even though there was absolutely no support anywhere in the system for a view to actually have multiple parents in practice; if you tried, everything would just crash or get into an endless loop. The only use case suggested in comments was that a cell could be a child of both a row and column in a grid, but that was just theoretical - it didn't allow for any functionality that couldn't be achieved some other way.
If you know how something should behave with a one-to-many relationship, and you know it's a use case that you might want to support, then yes, you should model it that way.
But, if you don't know how it would behave, and you can't think of a use case, then don't overcomplicate things.
Yes, a person can have more than one pet. But a person can only have one head, so why complicate things when there's no known use case for a two-headed person?
A system I worked on recently modeled users as potentially belonging to more than one organization, even though there wasn't a good use case for that and there wasn't any plan for how it should work if implemented. It made all of the code to get the organization from a user more complex than it should have been.
Another system I worked on had a view hierarchy that allowed a view to have more than one parent, even though there was absolutely no support anywhere in the system for a view to actually have multiple parents in practice; if you tried, everything would just crash or get into an endless loop. The only use case suggested in comments was that a cell could be a child of both a row and column in a grid, but that was just theoretical - it didn't allow for any functionality that couldn't be achieved some other way.
If you know how something should behave with a one-to-many relationship, and you know it's a use case that you might want to support, then yes, you should model it that way.
But, if you don't know how it would behave, and you can't think of a use case, then don't overcomplicate things.
Yes, a person can have more than one pet. But a person can only have one head, so why complicate things when there's no known use case for a two-headed person?
It sounds like the article is mostly advocating for a “one parent to many children” scenario, where the first organizations/users example you gave would be “many to many”, and your second view example is “one child to many parents”.
> But a person can only have one head
Not so: https://en.wikipedia.org/wiki/Polycephaly
Not so: https://en.wikipedia.org/wiki/Polycephaly
Are you sure it's one person with two heads and not two sharing a body?
I coined the term PAGNI - Probably Are Gonna Need Its - for exactly this kind of thing. There are exceptions to YAGNI which are things which don't cost much to add at the start of the project but are really expensive to add later on. https://simonwillison.net/2021/Jul/1/pagnis/
I do agree with you that many-to-many for everything isn't always the right choice though. It's a pretty tough call to make.
I've had plenty of conversations with product people where I've said "Are you absolutely SURE that the user will only ever have ONE of this thing? There are no possible situations where they might have more than one?" and had the answer "Well, OK there's this one rare case where they might need more than one... but it's hardly ever going to happen..."
I do agree with you that many-to-many for everything isn't always the right choice though. It's a pretty tough call to make.
I've had plenty of conversations with product people where I've said "Are you absolutely SURE that the user will only ever have ONE of this thing? There are no possible situations where they might have more than one?" and had the answer "Well, OK there's this one rare case where they might need more than one... but it's hardly ever going to happen..."
Something else to consider: Even if you start with a 'to-one' model, if your relationship is mutable, then your relationship is really to-many over time. Serial 'to-one' relationships end up being to-many anyway.
For example, you start off saying 'customers should have an email address'. And you build a lot of stuff on the assumption that customers have a single email address. But then customers insist that they want to be able to change their email address. And you'll find yourself in a better place if handle that by making email addresses into a collection, adding another email address to the customer, and changing which one is marked as 'current', than if you just overwrite the single email address.
For example, you start off saying 'customers should have an email address'. And you build a lot of stuff on the assumption that customers have a single email address. But then customers insist that they want to be able to change their email address. And you'll find yourself in a better place if handle that by making email addresses into a collection, adding another email address to the customer, and changing which one is marked as 'current', than if you just overwrite the single email address.
(EU) Keeping past data can be a liability. When handling personal information you need to prove that you are minimizing the amount of PII processed, and comply with data corrections/erasures as directed by your users, when those rights apply.
On those scenarios, a 'to-one' model is an implicit requirement imposed by law.
On those scenarios, a 'to-one' model is an implicit requirement imposed by law.
You're right, of course, GDPR made many to many relationships illegal, even when used as an illustrative example.
The piece might have benefited from at least some nods to relational algebra?
Years ago I worked on a system that had relations as a convenient in-memory data-structure. The jump in productivity for expressing business logic felt like going from C to Python. Operations like maps and filter and joins etc are very powerful.
(Funny enough, we used a lot of 'relational object mappers', because the other systems we dealt with were more object oriented so we had to translate into our own relational view.)
Years ago I worked on a system that had relations as a convenient in-memory data-structure. The jump in productivity for expressing business logic felt like going from C to Python. Operations like maps and filter and joins etc are very powerful.
(Funny enough, we used a lot of 'relational object mappers', because the other systems we dealt with were more object oriented so we had to translate into our own relational view.)
I think you're right. Probably due for a follow-up post.
EDIT: what was the system, by the way?
EDIT: what was the system, by the way?
Standard Chartered Bank's in-house Haskell dialect (and supporting libraries).
Codd's original paper about how relational databases are more flexible than hierarchical databases applies just as well when comparing relations against key-value stores like Python's dictionaries (or Haskell's Data.Map, or 'normal' ways to structure data in applications, which can be seen as the equivalent of statically typed dictionaries.)
See https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
Codd's original paper about how relational databases are more flexible than hierarchical databases applies just as well when comparing relations against key-value stores like Python's dictionaries (or Haskell's Data.Map, or 'normal' ways to structure data in applications, which can be seen as the equivalent of statically typed dictionaries.)
See https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
Yes! I dream of a language were the relational table is the primary data structure.
Oh, it wasn't a primary data structure, yet alone 'the' primary data structure.
It was just provided via a library and very easy to use. Haskell is pretty good about making user-provided libraries as convenient to use as things built into the language.
It was just provided via a library and very easy to use. Haskell is pretty good about making user-provided libraries as convenient to use as things built into the language.
These are not programming languages per se, but give VDM-SL or Alloy Analyzer a look. I know that particularly in the latter, anything can be joined or composed against anything since all values are relations.
I would more suggest modelling what you want, and defaulting to 1:n if you aren't sure but not always using 1:n
There are many cases where its important things are 1:1.
I think the core impulse is to consider 1:n as an extension of 1:1. I think that is backwards. 1:1 is an extended version of 1:n with a specific restriction n=1. Often this restriction is critically important to the app. When you need 1:1 you should use it. If you don't know, use the generic 1:n form.
P.s. weirdest clickbait headline ever.
There are many cases where its important things are 1:1.
I think the core impulse is to consider 1:n as an extension of 1:1. I think that is backwards. 1:1 is an extended version of 1:n with a specific restriction n=1. Often this restriction is critically important to the app. When you need 1:1 you should use it. If you don't know, use the generic 1:n form.
P.s. weirdest clickbait headline ever.
Agree -- 1:n is the safest approach if you don't know, but 1:1 has its place.
I think the core thing I've learned over time (in a startup) is that I rarely actually know for sure if there should be more of something, and 1:1 tends to create more "permanent" or "hard to reverse" changes to the way you design your system.
I think the core thing I've learned over time (in a startup) is that I rarely actually know for sure if there should be more of something, and 1:1 tends to create more "permanent" or "hard to reverse" changes to the way you design your system.
> But in most software systems I've worked with, Many-To-Many relationships are often the most complex, the most difficult to reason about, and the most difficult to maintain, because they introduce a third object -- the Relationship itself.
From the database perspective, the one-to-many model already requires introducing the third Relationship object. If you have "persons", and you have "pets", you're probably going to end up with "person_pets".
Yes, for a one-to-many you can certainly just stick an "owner" column in "pets" instead of having a separate "person_pets" table (ideally called something more sensible like "pet_owners"), but splitting it up doesn't really entail much more effort.
From the database perspective, the one-to-many model already requires introducing the third Relationship object. If you have "persons", and you have "pets", you're probably going to end up with "person_pets".
Yes, for a one-to-many you can certainly just stick an "owner" column in "pets" instead of having a separate "person_pets" table (ideally called something more sensible like "pet_owners"), but splitting it up doesn't really entail much more effort.
In my experience most apps designed around relational databases will benefit from:
- one-to-many and many-to-many mappings
- soft delete
- log tables / append-only tables
- entity-attribute-value
- meta columns (creator_id, timestamp, version, etc)
Beginning with them is way easier than patching afterwards
Beginning with them is way easier than patching afterwards
Working with PHP and Doctrine, this is something I've been doing anyway as an optimization, since 1-1 associations can't be lazily fetched. More of a workaround for a deficiency in the tool though: as an architectural decision to replace all 1-1 associations because "you might need it later" (YGNI?), it seems pretty suspect.
not all relations, certainly -- but it's a sane "if you aren't sure what to use, do this"
The Python examples in the article aren't very convincing, because they'd be so easy to change later that YAGNI says to start with 1:1. As other comments hint, things like database schemas, which are much more annoying to change, would be more convincing.
I did some work at a distribution company where 25 years earlier someone said orders would be either "machine" or "parts". Everything was built on that, and until very recently when they spent hundreds of millions to replace with an ERP, it remained.
Want a PC with an incompatible separate part? Can't be done. Create two orders please.
Unrelated but same place, during some profiling I wondered why the massive server spent 1% of its time on a user table scan. It was converting the entire user base's username column from varchar to user_name()'s nvarchar on every interaction with the DB. These guys (and the database optimiser) were all really solid, but stuff sneaks in.
Want a PC with an incompatible separate part? Can't be done. Create two orders please.
Unrelated but same place, during some profiling I wondered why the massive server spent 1% of its time on a user table scan. It was converting the entire user base's username column from varchar to user_name()'s nvarchar on every interaction with the DB. These guys (and the database optimiser) were all really solid, but stuff sneaks in.
Given enough time, there is no such thing as a 1:n relationship.
I thoroughly disagree. One-many can be converted to many-many, but many-many can never be converted to one-many. So start with one-many if you are not sure, then you have 2 options instead of 1. Maximize options not complexity.
When you're writing a novel as a novice, you often use a lot of singular nouns. But changing singular nouns to plural nouns is hard! And what are the chances you end up with really just one of those things you're writing about? If you just start with plural nouns, then you've got all the other numbers covered, and if you do end up writing about just one of the things, then you can pretend you're just using formal language, or the "royal we". You can't lose!
That's what reading this feels like. WTF? Nowhere is it mentioned that you should understand what you're building. The actual way to solve this problem is to learn more about what you're doing, and model it well. Don't start driving by turning right because right turns are easier, that's completely insane. If you don't know where you're going, stop driving and figure it out.
That's what reading this feels like. WTF? Nowhere is it mentioned that you should understand what you're building. The actual way to solve this problem is to learn more about what you're doing, and model it well. Don't start driving by turning right because right turns are easier, that's completely insane. If you don't know where you're going, stop driving and figure it out.
When users are only doing one-to-one right now, that's what you're going to be designing the UI for. Nobody likes a pointless dropdown to select the one possible option, after all. So how do you build a "go to pet" button? You just take the first element of the list.
But that's going to silently break the moment you start allowing one-to-many, because that button cannot exist anymore. It'll still compile without any issues - it'll just be fundamentally broken. So when transitioning to one-to-many you still need to go over all code touching it to check that no shortcuts snuck in - but this time you don't have the assistance of a typechecker to verify that you rewrote it to accept a list instead of a single object.
I've worked on one too many projects where the stakeholders wanted "infinite adjustability": it's going to massively blow up your development time, make the UX a living hell, and end up never getting used at all. Unless you already have an "upgrade to one-to-many" backlog ticket planned and the project is just one-to-one for now to get the MVP out the door, starting with one-to-many just because you might need it at some point in the distant feature is almost certainly a bad idea.