The biggest takeaway for me here should sound familiar: don't overengineer your data model. The more decisions you make about specific properties, the more special cases you will need to handle (and those are a continuing source of frustration for both programmers and users down the line).
In this case, most of these issues can be avoided by allowing a free text field instead of dedicated fields for street name, number, apartment, floor, state, county, district, and whatever.
The typical problem with what you're advocating is that in an enterprise environment typically your application is communicating with a number of external data sources that already make a bunch of assumptions about data, whether they're reasonable assumptions or not, and they typically expect that you'll be able to return them their data at the same level of granularity as they've provided it to you. E.g. if your client's 70s COBOL application gives you a list of their customers with addresses including a postcode field and then your application filters that or something and sends back a second list, it won't be acceptable to give them back addresses with the postcode simply concatenated into a text field with the other parts of the address.
The correct way to handle this is to be able to dynamically accept and store arbitrary granularity of data (and be able to translate that into a single text field at runtime if needed).
> In this case, most of these issues can be avoided by allowing a free text field instead of dedicated fields for street name, number, apartment, floor, state, county, district, and whatever.
The problem is that free form text fields are the opposite of over-engineering, because you're exchanging complexity at input time for the impossible task of parsing what the user typed in later on. Garbage in, garbage out.
An optimal way to tackle complex addresses is allowing the user to fill a free-form address, but annotate with meaningful metadata so you have useful data. Something like this:
Then you will have a problem if you want to filter the entries. You will need a good parser to identify that "Paris" means the capital of France or one of the many in the USA[1].
In the U.S., if you're mailing a large number of items, you can get significant discounts for providing the mail to the post office pre-sorted by the 9-digit ZIP code. To take advantage of that, your address schema would at least need to be able to extract that information.
One compromise would be to impose an address schema by default, but allow users whose addresses are incompatible with it to opt out of it and enter a free-form address.
In this case, most of these issues can be avoided by allowing a free text field instead of dedicated fields for street name, number, apartment, floor, state, county, district, and whatever.