3 ms·

I am surprised that their suggested solution was for a many-to-many relationship. Seems like you would want to stay one-to-many but remove the unique validation

by dyeje 8y ago

I am surprised that their suggested solution was for a many-to-many relationship. Seems like you would want to stay one-to-many but remove the unique validations. If you're sharing address records then it seems there could be many possible frustrating cases. For example: What if someone goes to create an address, typos it, matches another customer's address, and then fixes it updating the other customer's address in the process? The other customer might accidentally send their luxury item to a stranger if they aren't paying attention during checkout. The use case with married couples is compelling, but similar to the previous scenario, what if they got divorced? The one that moves updates their address, which updates their ex's address as well. Same as the other scenario, you might end up with a customer sending their item the wrong place. These might be edge cases but in general I think it's dangerous to change things like an address without an explicit action from the user.

Risord 8y ago
I used once unique many-to-many addresses by making them immutable. You don't update update them, just add new one to customer and detach old one.
- pluma 8y ago
  Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses. That said, the story doesn't make any sense to me to begin with (privacy and junk mail concerns notwithstanding): If an address is to be shared between two people it should probably not include the recipient. But then what mailbox do you address it to if there is more than one person at the address? How do you handle business addresses (where the full shipping label may include a person, an apartment and a company)? If you keep all that info out of the "address", what is even the point of having them as separate entities? If not, how do you avoid accidentally leaking personal information if the user filled out the form incorrectly and you re-match the address to another user? If your answer is "only match if it's an exact match", how do you handle synonymous addresses (e.g. "5th Street", "Fifth Street", "5th St.")? If you treat them as distinct, why bother matching at all? Or simply: what do you gain from having addresses as separate entities (regardless of whether it's a many-to-one or many-to-many relationship with users)? If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate? Have the address include the full recipient and stop trying to be clever.
  - marcosdumay 8y ago
    > Or simply: what do you gain from having addresses as separate entities A single person has many addresses. That alone already requires the addresses to be another entity.
  - Risord 8y ago
    > Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses. I am not sure do I follow correctly but just send marketing to addresses which are linked to actual users. Note that some addresses are orphaned from users but not some other events like orders. > If an address is to be shared between two people it should probably not include the recipient. I don't remember details about how recipients were handled. The system was quite simple and just about business addresses. We didn't pretend that address map strictly to single physical place so synonymous addresses were considered distinct. Therefore if a recipient was part of an address that was just another distinction point. > what do you gain from having addresses as separate entities > If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate? In our case customer was always company which may have quite a lot of different addresses for different offices or departments. Addresses also had hight expected reuse and we exposed saved address as a "thing" for a user so that it's easy to browse customers own orders history for different addresses etc. Uniqueness has been a quite neutral thing. Effectively it just removes case where the same customer has multiple equal addresses... Not much gain nor pain. Edit: At the end immutable shared entity is not much different from mutable and copied values. That's just one way of doing it and so far it hasn't back fired.
gshulegaard 8y ago
I am not surprised because the problem wasn't correctly defined: > At the database level, they had a "one-to-many" relationship between customers and addresses. Which the author goes on to say causes: > That was their first problem. A customer's partner might come into Bob's and order something and if the address was entered correctly it would be flagged as "in use" and we had to use a different address or deliberately enter a typo. But that's not true. The problem being described is not caused or implied by a one-to-many relationship between customers and addresses. The actual problem is that the address table has unique constraints defined in such a way that duplicate addresses are not allowed. So given that the problem was incorrectly defined as one-to-many being the root cause, it makes sense that the proposed solution is many-to-many. Just to be clear, many-to-many in this use case makes a lot of sense and depending on what the schema looks like it may be the best solution. I am just saying that the problem described was not because of "one-to-many". For example I work on a multi-tenant web app and we store emails (which have a similar problem with mailing/billing addresses) with a one-to-many relationship to accounts. We don't have a problem with an e-mail being used in several accounts...because our constraints allow it.
- thirdsun 8y ago
  > But that's not true. The problem being described is not caused or implied by a one-to-many relationship between customers and addresses. The actual problem is that the address table has unique constraints defined in such a way that duplicate addresses are not allowed. Furthermore the unique constraint most likely didn't even serve its purpose: How can you really make sure that an address is unique? There are countless ways to write one and the same address - in its simplest form it might be variations of Street / Str. or Avenue / Ave. Any information combining multiple lengthy strings will be prone to resulting in several slightly different variations.
rossdavidh 8y ago
In the case of businesses especially, it is often the case that many people have the same (work) address. And, if that business moves, you want all of those people to have the new address, without having to find them all and change all of them. Their work address is the Acme Business Address, id=573, which should be changeable once, rather than finding every record of every person there and changing it. But, the same person could easily have more than one address as well. Many-to-many matches that actual underlying reality, which is what is most likely to work well in the long run.
- HelloNurse 8y ago
  Example gratuitous complications: I don't want my address updated if my neighbour shares my address and he moves; if my partner breaks up with me on really bad terms and I move I don't want to share my new address. Probably, what's wrong is the concept of people having a relation with addresses while the actual relation is between a shipping and its target address. An example of doing it right: Backerkit (a site for crowdfunding-related surveys and orders) asks for an address every time there's something to send, and only later suggests using a similar previously entered address instead. No "updating", only separate transactions with their respective shipping addresses, all explicitly decided by the customer with some assistance in data entry.
  - rossdavidh 8y ago
    Hmmm...I see what you're saying, but if my business moves, and every person who deals with that company has to update their work address, and in the meantime it kept suggesting the old obsolete address that five other people who work there had already corrected, I would not be impressed.

by dyeje 8y ago

Risord 8y ago

I used once unique many-to-many addresses by making them immutable. You don't update update them, just add new one to customer and detach old one.

pluma 8y ago
Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses. That said, the story doesn't make any sense to me to begin with (privacy and junk mail concerns notwithstanding): If an address is to be shared between two people it should probably not include the recipient. But then what mailbox do you address it to if there is more than one person at the address? How do you handle business addresses (where the full shipping label may include a person, an apartment and a company)? If you keep all that info out of the "address", what is even the point of having them as separate entities? If not, how do you avoid accidentally leaking personal information if the user filled out the form incorrectly and you re-match the address to another user? If your answer is "only match if it's an exact match", how do you handle synonymous addresses (e.g. "5th Street", "Fifth Street", "5th St.")? If you treat them as distinct, why bother matching at all? Or simply: what do you gain from having addresses as separate entities (regardless of whether it's a many-to-one or many-to-many relationship with users)? If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate? Have the address include the full recipient and stop trying to be clever.
- marcosdumay 8y ago
  > Or simply: what do you gain from having addresses as separate entities A single person has many addresses. That alone already requires the addresses to be another entity.
- Risord 8y ago
  > Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses. I am not sure do I follow correctly but just send marketing to addresses which are linked to actual users. Note that some addresses are orphaned from users but not some other events like orders. > If an address is to be shared between two people it should probably not include the recipient. I don't remember details about how recipients were handled. The system was quite simple and just about business addresses. We didn't pretend that address map strictly to single physical place so synonymous addresses were considered distinct. Therefore if a recipient was part of an address that was just another distinction point. > what do you gain from having addresses as separate entities > If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate? In our case customer was always company which may have quite a lot of different addresses for different offices or departments. Addresses also had hight expected reuse and we exposed saved address as a "thing" for a user so that it's easy to browse customers own orders history for different addresses etc. Uniqueness has been a quite neutral thing. Effectively it just removes case where the same customer has multiple equal addresses... Not much gain nor pain. Edit: At the end immutable shared entity is not much different from mutable and copied values. That's just one way of doing it and so far it hasn't back fired.

gshulegaard 8y ago

I am not surprised because the problem wasn't correctly defined: > At the database level, they had a "one-to-many" relationship between customers and addresses. Which the author goes on to say causes: > That was their first problem. A customer's partner might come into Bob's and order something and if the address was entered correctly it would be flagged as "in use" and we had to use a different address or deliberately enter a typo. But that's not true. The problem being described is not caused or implied by a one-to-many relationship between customers and addresses. The actual problem is that the address table has unique constraints defined in such a way that duplicate addresses are not allowed. So given that the problem was incorrectly defined as one-to-many being the root cause, it makes sense that the proposed solution is many-to-many. Just to be clear, many-to-many in this use case makes a lot of sense and depending on what the schema looks like it may be the best solution. I am just saying that the problem described was not because of "one-to-many". For example I work on a multi-tenant web app and we store emails (which have a similar problem with mailing/billing addresses) with a one-to-many relationship to accounts. We don't have a problem with an e-mail being used in several accounts...because our constraints allow it.

thirdsun 8y ago
> But that's not true. The problem being described is not caused or implied by a one-to-many relationship between customers and addresses. The actual problem is that the address table has unique constraints defined in such a way that duplicate addresses are not allowed. Furthermore the unique constraint most likely didn't even serve its purpose: How can you really make sure that an address is unique? There are countless ways to write one and the same address - in its simplest form it might be variations of Street / Str. or Avenue / Ave. Any information combining multiple lengthy strings will be prone to resulting in several slightly different variations.

rossdavidh 8y ago

In the case of businesses especially, it is often the case that many people have the same (work) address. And, if that business moves, you want all of those people to have the new address, without having to find them all and change all of them. Their work address is the Acme Business Address, id=573, which should be changeable once, rather than finding every record of every person there and changing it. But, the same person could easily have more than one address as well. Many-to-many matches that actual underlying reality, which is what is most likely to work well in the long run.

HelloNurse 8y ago
Example gratuitous complications: I don't want my address updated if my neighbour shares my address and he moves; if my partner breaks up with me on really bad terms and I move I don't want to share my new address. Probably, what's wrong is the concept of people having a relation with addresses while the actual relation is between a shipping and its target address. An example of doing it right: Backerkit (a site for crowdfunding-related surveys and orders) asks for an address every time there's something to send, and only later suggests using a similar previously entered address instead. No "updating", only separate transactions with their respective shipping addresses, all explicitly decided by the customer with some assistance in data entry.
- rossdavidh 8y ago
  Hmmm...I see what you're saying, but if my business moves, and every person who deals with that company has to update their work address, and in the meantime it kept suggesting the old obsolete address that five other people who work there had already corrected, I would not be impressed.