How should multi-line street addresses be stored to enable structured queries?

How should multi-line street addresses be stored to enable structured queries?

• A. Use a JSON field exclusively for the address.
• B. Store all in a single unstructured text field.
• C. Use separate fields for line1 and line2 and optionally a normalized combined field or address_key; keep an unparsed street field for flexibility if needed.
• D. Do not store a separate line1/line2.

Answer: (C)

Structured storage that breaks the address into dedicated parts lets you query and validate directly on the components. By keeping line1 and line2 as separate fields, you can reliably search by street address while also accommodating secondary details like apartment numbers without mixing them into the main street data. A normalized combined field or an address_key provides a quick, stable reference for exact matches, deduplication, and joining with external systems, which speeds up lookups and keeps comparisons consistent across records. Having an unparsed street field as a fallback preserves the original input and offers flexibility for unusual formats or future parsing rules without losing the ability to perform structured queries on the main components. Storing everything in a single unstructured field or in a JSON blob makes targeted querying and indexing more cumbersome and error-prone, and skipping separate line fields reduces granularity and flexibility when filtering or standardizing data.

Structured storage that breaks the address into dedicated parts lets you query and validate directly on the components. By keeping line1 and line2 as separate fields, you can reliably search by street address while also accommodating secondary details like apartment numbers without mixing them into the main street data. A normalized combined field or an address_key provides a quick, stable reference for exact matches, deduplication, and joining with external systems, which speeds up lookups and keeps comparisons consistent across records.

Having an unparsed street field as a fallback preserves the original input and offers flexibility for unusual formats or future parsing rules without losing the ability to perform structured queries on the main components. Storing everything in a single unstructured field or in a JSON blob makes targeted querying and indexing more cumbersome and error-prone, and skipping separate line fields reduces granularity and flexibility when filtering or standardizing data.