What is the best way to store addresses in a database (MySQL)?

Question

Database design

The task is typical, can you tell me the typical best solution?

In a MySQL project, you need to store the data of clients of their counterparties. And there and there are addresses.

I want to make a separate entity-table for the addresses, put them there, and then simply refer to them as foreign keys.

Question on the structure of the table:

- Is there any typical OPTIMUM solution for this case?

Ie:

- What are the fields?
- Maybe it's better to spread it into several tables?

In general, everyone is doing this, many generations already))) right?

Has a standard been developed?

Where to get it?

Thank you.

upd.:based on the answers from the caps (guys, I'm kind :) - yes, of course, we are NOT talking about writing addresses in one line, we are talking about address quantization :) the question is - what kind of fields to do for this? Do countries and cities need to be placed in separate tables (99% of what is needed)? in general, I want to get some ready-made solution, or find out where to get it.

Answer 1

Well, firstly, if you already have different entities that can refer to the same address, then of course you should put the addresses in your table, these are the requirements of NF in building the database. This is how we reduce duplication.
But further along the structure of the table itself, you already need to dance from the task. If you just select the entire address, then just store the entire address in a line. If you need to find all the houses under the number "6", then it is logical to put the house number in a separate column, etc.
Well, in general, I would immediately cut the address, because. today we do not need to look for houses with number 6, but tomorrow we do.
The question of how many parts of the address to cut is also a good question. At least the house number needs to be separated.

Answer 2

According to the classics, there are 2 above-mentioned approaches: Break the address into composite entities and write down everything that is possible in the form of keys, and write the address in 1 line, scoring all sorts of quick aggregations / selections. What suits specifically in your case is absolutely not clear from the question, since it does not contain the main thing - how it is used.

Answer 3

Now many are inclined to the maxim "everything as a service"
As a result, you have a sign with a link to the address (id) and the full line of the address
If necessary, request.
Another option is to parse the address on your side. For Russia, I did something similar look in my git