A Better Way to Store Address Data in a Database

An address is a physical location in the real world. It’s something we often need to store in our database, and there are several ways it can be done.

In this video, I’ll show a method that’s commonly used, highlight some issues that could happen using this method, and explain a better way of storing the data.

It’s not the “single best way to store addresses”, as there are other variations that you may want to use or prefer to use in your application, but it should help you when designing address fields.

We’ll also look at a couple of queries using the new design to get the data you might need.

Timestamps:
00:00 A common way to store addresses
00:42 Problems with addresses
02:17 The solution
04:51 SQL to create the table
05:48 One final tip - moving addresses

[yassinebouchoucha]

always brainstorming for hours about how should we store the address! thank you for the solutions benchmarks.

[philiperiskallaleal]

Dear Database Star, thank you for this amazing well explained video. One suggestion I would make for the problem of a location is the concept of Country. Not always is clear the political definition of the place that is being addressed. With this in mind, one internationally well accepted solution is the one adopted by the World Health Organization (WHO) which adopts a self-related table of Administrative Political Divisions. Each of these will have a property (varchar or something mappable to an ENUM) that defines its type (e.g., country, state, city, street, etc.). For instance, one big mistake is to assume that an administrative political division as the United Arab Emirates is a country. Further more, as you well, pointed out in this video, not every country has state subdivision. Therefore, the WHO's approach sanates all these problems.

[gabrielgrigore]

Insanely helpful, can't thank you enough for these, so happy I stumbled upon this channel.

[saireddyksr]

Yes, YOU ARE A DATABASE STAR 🌟.
Easy To Understand Your Teaching and video Presentation.
Keep Doing More Videos ♥️

[budmonk]

Thank you Ben - always thought about these during design but never followed through this thought process.

[yours.trustworthy]

Extremely good I was searching for such simple explanation
Thanks a ton

[ameename]

Wow....everything just makes sense...very informative video. You just saved me from making a very huge mistake. Thank you🤝

[Deltacasper]

I would like to see videos for the other addressing designs you mentioned such as ip address, names, profanity words and emails including how to validate them.

[konstantinterekhin]

What if this database needs to handle high load? All these joins will slow it down, especially many to many. So I guess denormolized(intro design) is better in this case?

[mark]

Thanks for the great video, quick question why have a many to many relation between the customer and the address instead of doing a one to many relation where if a user is deleted, or a user deletes an old address the user_id would be set to null.

[hojasderuta]

“A customer can have many addresses” but …what would be the statement for the reverse to sustain a many to many relationship? “An address can be had by many customers?” Could you please clarify? Awesome videos by the way.

[emilejuniorada]

Hi, thanks for the great video. I'm in a case where i have 2 address for one entity (typically a start_address and end_address). How should i handle it ?

[vyacheslavkapitonov]

Hello!
Why don't we create separate tables for city and regions as we did with country?

[rafsanmaruf]

Informative video. Please make this types of video. Thank you...

[ameename]

Looking forward into more videos like this...🖤

[dnaHiFi]

great video database star. quick question, why do we create a separate table just for country?

[jasonsealy]

Hey Ben, you have been tremendous help. How would you model the data in a situation where you have 2 entities such as "employee" and "project". Where the project table would only need attributes such as "city", "region". Whereas the employee would need the attributes listed in the video. Would having separate address entities for employee and project be redundant or a good practice? Thanks again!

[help]

Nice video as always, ensuring validation of past record never cross my mind. I have a question, currently I am developing a project for practice. It has 4 types of user, `patient`, `dentist`, `dental assistant`, and `admin`. Should I put all users in 1 table with role column, or separate them to different tables.

[mhmdshaaban]

Is it a good idea to create a separate table for states and cities as well?

[flavio]

Muito bom seu vídeo. Aprendi muito mesmo sem saber inglês, somente pelas imagens e animações..