Storing a structured address vs. a string for a user's address

Question

Storing a structured address vs. a string for a user's address

David Thielen 3,211

Hi all;

I am diving in to using the Azure Maps service and while it can accept a StructuredAddress, it seems to prefer that it get the address as a string. In other words, it wants:

var addr = "15127 NE 24th Street, Redmond, WA 98052 US"

instead of:

var address = new StructuredAddress
{
    CountryCode = "US",
    StreetNumber = "15127",
    StreetName = "NE 24th Street",
    Municipality = "Redmond",
    CountrySubdivision = "WA",
    PostalCode = "98052"
};

So, happy to get your opinions too, but primarily what are the specific trade-offs here between the two ways of storing the data. What I can think of is:

String advantages

DB schema - single column in the database.
U.I. - free form single edit box is quicker/easier to enter

Structured advantages

Get all components
Clear what each component is

With either approach, when the user enters their address I will pass it to the service and then use the returned verified address.

So any other trade-offs?

thanks - dave

Sedat SALMAN 14,180 Reputation points MVP

2023-05-22T07:08:01.1166667+00:00

When deciding between storing a structured address or a string for a user's address, there are several trade-offs to consider.

Simplicity: Storing the address as a single string simplifies the database schema since you only need one column to store the entire address. This can make the data management and querying process easier.

User interface: Using a free-form single edit box for entering the address allows users to quickly and easily input their address. It eliminates the need for separate input fields for each address component (street number, street name, city, etc.), which can be more user-friendly.

Data integrity: Storing the address as a structured object allows you to ensure data integrity and consistency. Each address component (street number, street name, city, etc.) has its own designated field, making it clear what each component represents. This helps prevent data entry errors and inconsistencies.

Flexibility: Storing the address as a structured object provides flexibility when working with the data. You can easily access and manipulate individual address components, perform validation checks, and format the address according to different requirements or standards.

Integration with services: Some services, like Azure Maps in your case, may prefer or work more efficiently with structured addresses. By storing the address in a structured format, you can easily pass the address components to these services without the need for additional parsing or formatting.

Future scalability: Storing the address as a structured object allows for future expansion. If you need to add additional address components or integrate with other services that require more detailed address information, having a structured format will make it easier to adapt to those changes.

Considering these trade-offs, you need to assess your specific requirements and priorities. If simplicity and ease of use are crucial, storing the address as a single string may be sufficient. However, if data integrity, flexibility, and integration with services are important, storing the address as a structured object would be a better choice.

It's worth noting that you can also consider a hybrid approach where you store the address as a structured object in the database but provide a user interface that accepts a single string for input. You can then parse the string and populate the structured address object behind the scenes.

Ultimately, the decision depends on factors such as your application's requirements, the need for integration with services, data integrity concerns, and the trade-offs you are willing to make.

Accepted answer

0 additional answers

Your answer

Answer 1

If you are using the Azure Maps V2 Search geocoding service, behind the scenes it leverages the Bing maps geocoder. That geocoder is well known to perform better with freeform address strings than with structured addresses. This is documented in the Bing Maps docs here: https://learn.microsoft.com/en-us/bingmaps/getting-started/bing-maps-api-best-practices#use-the-find-by-query-api "The recommended method is to pass in addresses as single-line queries using the Find by Query API." where the "Find by Query" API is Bing Maps free form address string geocoder API. This insight hasn't made it into the Azure Maps documentation yet, I suspect because this API is still in preview.

As for storage, what and how to store should be based on the needs of your scenario. If you need access to parts of an address, store that. If you need just the address string, store that. If you have users passing in address strings into a UI and want a cheap autosuggest based on addresses in your system, store the free form string. There is nothing wrong with storing both if you are fine with the additional storage space (likely relatively small unless you are working with tens of millions or more addresses, but even then, likely not a big cost. Also be sure to store the latitude/longitude value for fast loading/visualizations.

Share via

Storing a structured address vs. a string for a user's address

0 additional answers

Your answer