3 ms·

This sounds to me like a "just draw the rest of the owl" solution. You're right in that there's nothing you can do with JSONB that you can't do with a custom t

by hug 5y ago

This sounds to me like a "just draw the rest of the owl" solution. You're right in that there's nothing you can do with JSONB that you can't do with a custom type, but the larger problem is "do I even know how to make a custom type that might fit all of the various inconsistencies that I might face with this data?" The answer, for me, is no. I have no idea, and I'm not going to pretend. I can throw it all into a giant chunk of JSON and leave explicit note that address handling isn't, and at least that way no one actually thinks that it is, which is probably safer for everyone involved.

jhgb 5y ago
In my case, I actually do know that; there's even a legal definition of what an address is and there's a national registry of all addresses. The problem is that I can't solve this for every country myself since I only have national knowledge (which is sufficient for my needs, fortunately).
- psd1 5y ago
  Sounds like, for your purposes, you can define an address type that you can map to columns. But I think you're the outlier. We have fairly regular addresses in the UK. You can enforce a country and a postcode. You can enforce at least two lines of the local address. That's where it ends. I live right now I'm a house that's described as one village everywhere except by the local council, who address us by the other village. We're in a lane between the two. But the lane is impassable in the middle except with an off-road vehicle. Google maps doesn't know that. Visitors get lost even with GPS and precise co-ordinates! Before moving out here, I've frequently had to just enter "London" twice due to aggressive validation. I've even entered "London, London, Greater London". It works, but I know there's a senior engineer somewhere who's obstinate and wrong. Hell, I lived somewhere where flats B, C and D for our house number were a different door to A, because properties can be converted. What is the value of structuring this data? Store a country, a postal code, and a multiline local field, and don't try to validate that field except maybe to clean up surplus whitespace.
  - jhgb 5y ago
    When validation and parsing fails, there should be room for local fixes. There's an interesting question as to what extent should corner cases be code-driven and to what extent they should be data-driven. Your case definitely sounds like one of those things that would need to be fixed by hand and then associatively recalled (so that they'd need to be fixed only once). > What is the value of structuring this data? Well, for example I definitely need it for analytical purposes, so I have to try.
    - dlainhart 5y ago
      > Well, for example I definitely need it for analytical purposes, so I have to try. Try accepting the inherent limitations of trying to analyze something so unruly and take your analysis with a larger grain of salt instead of forcing order on something so inherently ad hoc.
- thatwasunusual 5y ago
  You have highlighted the problem I described in my original reply to the post, and although you have been pretty active in this thread trying to come up with solutions, you seem to summarise with "I don't know the answer" with this reply. Try imagining your application(s) that requires addresses moving into new markets, like Norway, Sweden, Ghana or whatever.

by hug 5y ago

jhgb 5y ago

In my case, I actually do know that; there's even a legal definition of what an address is and there's a national registry of all addresses. The problem is that I can't solve this for every country myself since I only have national knowledge (which is sufficient for my needs, fortunately).

psd1 5y ago
Sounds like, for your purposes, you can define an address type that you can map to columns. But I think you're the outlier. We have fairly regular addresses in the UK. You can enforce a country and a postcode. You can enforce at least two lines of the local address. That's where it ends. I live right now I'm a house that's described as one village everywhere except by the local council, who address us by the other village. We're in a lane between the two. But the lane is impassable in the middle except with an off-road vehicle. Google maps doesn't know that. Visitors get lost even with GPS and precise co-ordinates! Before moving out here, I've frequently had to just enter "London" twice due to aggressive validation. I've even entered "London, London, Greater London". It works, but I know there's a senior engineer somewhere who's obstinate and wrong. Hell, I lived somewhere where flats B, C and D for our house number were a different door to A, because properties can be converted. What is the value of structuring this data? Store a country, a postal code, and a multiline local field, and don't try to validate that field except maybe to clean up surplus whitespace.
- jhgb 5y ago
  When validation and parsing fails, there should be room for local fixes. There's an interesting question as to what extent should corner cases be code-driven and to what extent they should be data-driven. Your case definitely sounds like one of those things that would need to be fixed by hand and then associatively recalled (so that they'd need to be fixed only once). > What is the value of structuring this data? Well, for example I definitely need it for analytical purposes, so I have to try.
  - dlainhart 5y ago
    > Well, for example I definitely need it for analytical purposes, so I have to try. Try accepting the inherent limitations of trying to analyze something so unruly and take your analysis with a larger grain of salt instead of forcing order on something so inherently ad hoc.
thatwasunusual 5y ago
You have highlighted the problem I described in my original reply to the post, and although you have been pretty active in this thread trying to come up with solutions, you seem to summarise with "I don't know the answer" with this reply. Try imagining your application(s) that requires addresses moving into new markets, like Norway, Sweden, Ghana or whatever.