Relationships: Start with Several

https://lukebechtel.com/blog/relationships-start-with-several

by lukebechtel • 2 years ago

57 34 2 years ago

eru4• 2 years ago

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.)

marviel4• 2 years ago

I think you're right. Probably due for a follow-up post.

EDIT: what was the system, by the way?

eru4• 2 years ago

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

dmazzoni• 2 years ago

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?

floppydiskette• 2 years ago

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”.

perilunar• 2 years ago

But a person can only have one head

Not so: https://en.wikipedia.org/wiki/Polycephaly

jj999• 2 years ago

Are you sure it's one person with two heads and not two sharing a body?

simonw• 2 years ago

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..."

crote• 2 years ago

I believe an approach like this is still likely to bite you later.

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.

rijx• 2 years ago

Can second this, it’s premature optimization. The real problem is usually having no easy and reliable way to migrate data / apply schema changes.

resonious3• 2 years ago

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".

marviel4• 2 years ago

"renaming" you lost me there, now you have a backwards incompatible API

eru4• 2 years ago

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.

bawolff• 2 years ago

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.

marviel4• 2 years ago

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.

resonious3• 2 years ago

I definitely though this article would be about something else...

dash2• 2 years ago

Talk about a clickbait headline ;-)

samatman• 2 years ago

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.

rvr_• 2 years ago

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

zarzavat• 2 years ago

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.

amiga1200• 2 years ago

Given enough time, there is no such thing as a 1:n relationship.

chuckadams• 2 years ago

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.

marviel4• 2 years ago

not all relations, certainly -- but it's a sane "if you aren't sure what to use, do this"

jameshart2• 2 years ago

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.

Rygian• 2 years ago

(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.

jameshart2• 2 years ago

You're right, of course, GDPR made many to many relationships illegal, even when used as an illustrative example.

feoren• 2 years ago

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.

richardw• 2 years ago

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.

thenoblesunfish• 2 years ago

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.

yellowapple• 2 years ago

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.

Relationships: Start with Several

Related Stories