How should I store in relational database geographical addresses?

Question

I am building a real - estate platform and I want to document about how to store addresses in database.

I have think some ways and show you, so please take me to the right way:

1. I tried to bring to the client the flexibility to choose what fields to use for address.

Here are some of them:

Then for each of this fields he can add values. Each value has an ID and can be assigned to a property.

For example street_number has 3 values.

At the end I have a table property_addresses where I correlate the property_id with the value_id. In the picture value 20 was selected for that property.

The problem is that I can't see how can I get a list of a full correct address.

Answer 1

Create a table (address_fields) which holds the different type of fields you need for storing the addresses. Another table to hold the details of property_id, field_id & field_value. You can add any new address type in address_fields table at any later stage. You must be storing the relation between property_id & user_id which you can use to create JOINS for pulling the data for your requirements.