3 ms·
Wrong solution and nothing to do with database design. Why do the addresses even need to be in the ecommerce system to do the mailing? Couldn't they be in anot
by manigandham 8y ago
Wrong solution and nothing to do with database design.
Why do the addresses even need to be in the ecommerce system to do the mailing? Couldn't they be in another table? Or a simple CSV? No reason to create fake customer records to send some spam. Something's missing in this story.
But if for some reason they are forced to use the same db, multiple customers sharing the same address doesn't mean they need to share the same row in the table. Keep the 1:many relation without the unique constraint. Customers can then have multiple addresses, even if they're shared with others.
Addresses are complicated too, so you might as well just have a country field + the rest and then use an API to standardize, catching errors and making queries easy.
- tynorf 8y ago> Addresses are complicated too so you might as well just have a country field + the rest and then use an API to standardize, which also makes future queries easy. This, big time. I worked on an ecommerce site that had to ship to Nigeria. No zip codes, often no street name or number. "Two doors north of the post office on the east side of Lagos." The couriers could find it.
- manigandham 8y agoThere is a system called Plus Codes: https://plus.codes/ https://plus.codes/ Designed as a way to address any location segment with a unique code, based on lat/long, and it's a great way to provide addressing for many areas that don't have formal systems.
- craftyguy 8y agoThose, in a way, seem even worse compared to a system based on reference points. Where is G2HR+GW2 in relation to G2HR+GWD? Couriers in locations that use this system would need access to decode that into something that they can use to find a location, and people at locations would need a way to encode their current location. The current zip code system in the US is similar, with the exception of having been in use for about 50 years, and the number being relatively easier to remember.
- manigandham 8y agoThe whole point of an addressing system is that it is structured. You current home address has nothing to do with reference points either, but it makes it easy to catalog and transfer in a standard format. This is to help transition areas that are still based on local knowledge to a standard system, without the expense of official street names and building numbers.
- craftyguy 8y ago> You current home address has nothing to do with reference points either, But it does. Based on my address, you would know what city and state I am in, and in many cases even which direction in that city my street is (e.g. NW Whatever St). Then you'd know where on the street I am because 3350 is one block over from 2428. You'd also know which side of the street I am because 3350 is across the street from 3347.
- rakoo 8y agoBut you don't know whether you need to turn left or right to go from your street to Church street. The comparison can only be done on one dimension.
- manigandham 8y agoGiven a complete lack of an address system in rural areas, any structure is better than nothing. If you're comparing address systems by how well local proximity can be inferred from a given address, then Lat/Long is the best system because it's global without language, national or political barriers, doesn't require formal naming, and has no arbitrary designations. Open Location Codes are based on lat/long, but designed to use alphanumerics to make it easier to remember while also working interchangeably with lat/long and country designations. Perhaps the developer page will help: https://plus.codes/developers https://plus.codes/developers
- strken 8y agoUsing normal lat/long to read a map doesn't require you to do a base20 -> base10 conversion, where the characters are 23456789CFGHJMPQRVWX instead of 0123456789ABCDEFGHIJ, and where latitude and longitude are interleaved character by character, in your head.
- nradov 8y agoMost US sellers use some sort of USPS certified address validation and cleanup software to catch data entry errors and prevent delivery problems. So dumping everything but the country into a single big field isn't necessarily a viable design. https://www.usps.com/nationalpremieraccounts/manageprocessandaddress.htm https://www.usps.com/nationalpremieraccounts/manageprocessan...
- manigandham 8y agoThat's why I said "use an API to standardize". I don't see why this is incompatible with having a single field. Unless you actually need the component parts, it's much easier to manage than the schema needed for all the variations.
- nradov 8y agoIt's not really practical to call the API every time you need a normalized address. Normalize once and ask for user confirmation, then store the clean address with a separate field per component.
- manigandham 8y agoSure, that's covered by "Unless you actually need the component parts" Either way, yes you should store the clean standardized data and not the input. I didn't think that was confusing. Also you can get the best of both options with a JSON field.
- wstuartcl 8y agoDO NOT DO THIS. Have you ever moved into a new house or apartment on a new street or address? Effectively blocking users because you assume their address is corrupt because your system is out of date. At some point you have to choose what is better, trusting that your users are able to enter their address or that an API (from USPS or one of the dozen or so others out there) is able to understand what is and is not a valid address.
- 8y ago