Imagine one-to-many relationship between Customers and Orders. It’s possible to configure the link between these tables from the side of Orders so that an order record cannot be linked to more than one customer record. If you create the record in an Orders view, this rule is enforced.
What I just realized though is that this rule CANNOT be enforced from the Customers table, that is, from the Many side of the relationship. As far as I can see there is no way to prevent a user from linking a customer to an order record that was previously created and linked to a different customer. Of course this doesn’t make sense and is a violation of the cardinality of the relationship.
It’s interesting that Airtable permits a kind of quick-and-dirty many-to-many relationship (without the creation of a join table) by allowing both sides of the relationship to link to many records. This is a convenience permitted by some other database systems as well (it’s possible for example in FileMaker). But if the designer of the base (viz., yours truly) wants to prohibit any given order from being linked to more than one customer, than it seems to me that prohibition should be possible.
The fact that it’s NOT possible seems like a design flaw to me, if not a bug. What think you?
