0

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:

enter image description here

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.

enter image description here

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.

Sebastian Corneliu Vîrlan
  • 1,652
  • 5
  • 28
  • 56
  • 2
    There's not going to be a "right" way to do this. It will depend on how you need to use the data later and whether you need to import or export data (and how that data is formatted). – techturtle Dec 22 '16 at 14:11
  • Think this through, is there any advantage to separating out the address info into smaller and smaller pieces? Is anyone ever going to search for all addresses that have street number of 20? The more complicated you make teh user interface, the less people want to use it. Really people are pretty good at adding the street address in one field and then city, state, country, postal code etc. That is how people search for things later. Trying to make it more complex just makes users angry. Flexibility is generally a bad thing in a user interface, it makes the software difficult to use. – HLGEM Dec 22 '16 at 16:46

1 Answers1

0

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.

Gurdev Singh
  • 1,996
  • 13
  • 11