How are databases designed for such sites?

Question

Database

Let's say there is a site for the sale of apartments. There, each apartment has a bunch of information that needs to be stored in the database, well, the question itself, how to store it correctly, I did like this prntscr.com/e4fvbp . But I thought to store everything in a json array, also in the database, but you don’t need to create 10 fields for each characteristic.
Maybe I'm doing something wrong or learning old material?
Maybe there is a more modern approach to this task in 2017?

Answer 1

How are databases designed for such sites?

1) Clear names for each field, in English (this is the standard, do not be lazy to look into Google translate for translation, and you will tighten up English yourself, and it looks professional, you are not ashamed of the code, etc.)
2) In fields where you can make a limited number of options, put a field of type int and a reference table associated with this field. So it will be easier and faster to select these fields in the case of a filter search.
3) Varchar is used only where the user enters something "by hand", in other cases it is almost always a link to a lookup table that lists the possible values.
As for elasticsearch, it is a search engine in text fields, powerful and convenient, but in this case redundant, you just need to design normally. It's like making a crooked slow code and then hang up caching so that it somehow works at an acceptable speed. Like, "skis don't go on asphalt, let's put the skier in... let's put in a more powerful rocket!".

Answer 2

Nothing is clear, "Address" has several addresses for one apartment, or is it "Addres"?
If there is only one address, and it is not required to separately store each street, house, etc., then there is nothing to change here, really varchar or text, and if we allow a lot, then either you need to create another table of addresses and link it, or store it as an array JSON, the second one is easier.
But the sections should be taken out into a separate section table and linked through FOREIGN KEY, unlike many-to-many relationships, a foreign key is implemented much easier, both in the database itself and in MVC frameworks.
Also, why everywhere varchar(200), if you want to initially have maximum flexibility, and optimize as needed, then it’s better TEXTthen.
And a trifle: the names of the columns, if the project is only yours or you are the main developer, then this is at your discretion, but usually a full-fledged translation is used instead of transliteration.

Answer 3

elasticsearch