Designing a database to store addresses for people or businesses is pretty common.
There are a few ways you may have seen it done,
and one way is storing all of the information in one field, to make it “easier”.
But, there’s a better way.
In this video, we’ll look at the problem with storing addresses in a single field,
and what a better solution could look like. We’ll also look at a better way
to store the address other than a field on a person or company table.
Let’s get into it.
An address is a physical location in the world. We often need to store an address
in our database to send letters, or packages from an online store, or for a range of other reasons.
We can store addresses for where people live or where businesses are located.
You might have seen addresses stored in a database like this,
in a single field called “address”. It would contain everything an address needs:
a street number, street name, suburb, city, maybe a state, postal code, and perhaps country.
It makes sense. An address is a single location, and storing it all in one field with a large
maximum field size seems like a good idea. You, or the person or team who designed this
particular table, might have preferred the flexibility of having it all in one field.
However, there are some drawbacks to this approach.
First, it’s very hard to filter your data on parts of an address.
Let’s say you want to find all addresses in the city of Seattle. You can write an SQL query that
has a WHERE clause and uses the LIKE operator with percent signs to find a partial match. However,
this can be slow as it’s unlikely to use an index and every address field needs to be searched.
It also could show false positives, such as addresses where the street name is Seattle.
It’s also hard to implement data validation. If your address field is a 2,000-character
text field, it’s hard to ensure that a state is selected from a specific list, for example.
There is a wide range of possible addresses in the world, so you may
not want to add a lot of validation, but having it in one field makes it harder.
Finally, it could make the user interface harder to work with.
If there’s a front-end for displaying or entering addresses,
having all of the data in a single field could make it hard to add a new address.
It’s possible, and of course, it depends on the application, but it’s something to consider.
So, what’s the solution to all of this?
There is a better way. And this is to store each component of the address in separate fields.
Here’s what it could look like.
Instead of a single field for address, we have a field for unit number. This could
be a single number such as 2, or number with letter such as 3A, or a range such as 3-4.
This would be a character field, as it’s possible for this to include letters.
We have a field for street number. It would be similar to the unit
number in the type of data it would contain, so it’s a character field.
The next set of information we need to capture is street name, street type,
and suburb. Some countries have this information in their addresses, but some countries don’t.
Some countries may not have a suburb, county, or shire.
If you’re confident you will always have those fields, then feel free to use them.
But for this design, we’ll add two fields called address line 1 and address line 2.
This will allow us to be flexible with the street name and type and location,
but still allow for validation in other fields.
You might be wondering, isn’t this just the same as putting the address all in a single field?
It’s similar. It all depends on the kinds of addresses you’ll be recording.
If you’re sending packages internationally, you may want to have a design like this
so you can send to places that don’t follow the same address standards you may be used to.
Or, if it’s a school enrolment system for a single city, you can have a stricter design
as you know the format and standard of the address. So, feel free to adjust this design.
Next, we can store the city. This is a text field to store city names,
and is pretty straightforward.
The next piece of information to store is the region or state. Some countries have states, some
have regions, some have provinces, some don’t have anything. You can call the field what you like,
and in our example we’ll use the name of region as it’s more general. This would be a text field.
The next field is the postal code or ZIP code.
The formats for this code are different in each country. In Australia they are four digits,
in the US they are five digits, and in the UK they are a combination of letters and numbers.
So, this should be a fairly small field but should be text and not just numbers.
Finally, we have a country field. We can store the name of the country here,
but I think it’s better to have a separate table that lists all countries,
and store the ID here as a foreign key. This makes validation easier.
Here’s what our design would look like. We’ve got a range of address fields in our customer table,
and a link to the country table.
What would the SQL look like?
If we want to create this table, here’s the SQL for it. We’ve got the fields we want to add to
the table here, along with the data types. This is the code for a Postgres database,
and the only thing that would be different for other SQL databases would be the data types.
What about selecting the data? Let’s say we have our table and it’s populated.
How would we select from it?
Here’s a query that would show all of the fields for records in the customer table.
We see the information in different fields. We also see a join to the country table,
so we can see the country name.
Here’s a query that can be used to filter addresses that are in a certain city.
We have a Where clause to limit records that have this specific city value.
The results show records in that city.
So, writing the SQL to show addresses from separate fields isn’t any more difficult.
If you want to make your database design even stronger,
then there’s one final tip I have for you.
Earlier, we said that an address is a location where someone lives or where a
business is located. We’ve treated it like an attribute of a person or a business.
What happens when a person or a business moves? Their record is updated with a new address.
However, the address itself, the address in the real world, does not change. It’s the
relationship that the person or business has with the address that is changed.
If I’m living at 25 Main St, and I move house to 10 Long Road, then the address of 25
Main St still exists. It may be empty or have someone else living there, but it still exists.
Rather than storing the address as an attribute,
we could store the addresses in a separate table and relate them to a person or business.
Why would we do this?
It will allow us to keep a history of addresses. We can see a person’s address or business address
change over time. This can be useful to ensure old customer orders or other records are still valid.
It also allows us to store multiple addresses for one person or business.
A business may have a mailing address, a physical address,
and other addresses. Having the address in a separate table allows for this.
Let’s check out the design for this.
Here’s what our design may look like. We have an address table,
with an ID as the primary key, and the same address fields we saw before.
If we want to store the concept of a customer having multiple addresses,
we would need a joining table, as it is a many-to-many relationship.
I’ve got another video here on many-to-many relationships if you want to know more about them.
In this joining table we can have the customer ID and the address ID.
We could add other fields such as whether it’s active, the date it was created,
or the address type, but for now we can have just these two fields.
So, this will allow us to store multiple addresses for a person or a business,
and ensure the quality of data is still good.
If you liked this video on how to design address fields in a database,
let me know in the comments below. I’ve got other ideas on database designs for things like email
addresses, people names, and IP addresses, so I’ll create videos on those if you find them useful.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.