Doubt with relational modeling

Asked

Viewed 167 times

5

I’m setting up a land registry, where these properties belong to an address, which belongs to a neighborhood that belongs to a city.

Ex tables:

Imoveis: id, endereco_id, etc...

Enderecos: id, nome, bairro_id, rua, numero, cep, etc...

Bairros: id, nome, cidade_id

Cidades: id, nome;

However, if I want to perform a real estate search in a registered city, as this relationship is, it is valid to duplicate the key within Immoveis, creating a city_id?

First situation: When searching selects, I want to list only cities that have registered properties.

Second situation: Select the city and list via ajax the neighborhoods that have property registration.

Third situation: search the properties and list along with them, their addresses, neighborhood and city.

Any idea how to work this data better, and whether I’m on the right track?

Note: I am not asking for ready queries, but ideas on how to best implement this relational model, where the registration of each neighborhood and city will be done along with other property information, consistently saving the names of neighborhoods and cities, avoiding the double registration of these.

mysql sql relational-model
  • 1

    My first question would be whether you really need to have this address normalization. Do you have any need or advantage? Can a property be in more than one address? There is a reason the address is separate?

  • 1

    Regarding the redundancy of ids, you might be interested in: http://answall.com/q/22208/7261 You may choose to have a column [tabela]_id redundant or use JOIN to make the query filtering by city. In the second situation, you can use a LEFT JOIN or RIGHT JOIN

  • @bigown A property belongs to a unique address. It is separate because the immovable table is large, and because I will not always call Addresses... There is the question of cascading update, if remove a city, or neighborhood, should go all that belongs to him/her together.

  • @Marcelo (only one comment...) for this would be enough to select the fields to use in select, not necessarily need to separate the table.

  • But for performance purposes, the smaller the table, there is no better performance in selects?

  • 1

    @Marceloaymone You tried both ways and measured if you won? Having to access data in two places worsens performance. If the table is smaller can have performance gain. But only if all data are in it. Separate can even organize a little more, this is it. But it’s rare to need this entire organization.

  • 2

    @Marceloaymone what will give the performance is index, and the amount of data that travels through the interface. There may even be a difference in file separation depending on the form you organize (for better or worse), but to worry about this type of adjustment, only in an immense or very critical application (it would involve a more complex analysis of the case).

  • Okay, actually, separate or unaddressed would not be my focus of the question but rather as to cities and neighborhoods, etc.

  • 1

    I didn’t answer because you already got an answer. Except for the separate address, your modeling is fine, just do the darlings correctly. In the answer has a good basis to do. City and Neighborhood are entities and not a description, so you did the correct. Then you’ll have to see if you can implement any way to find nearby neighborhoods in a radius. It’s much more complicated but it’s very useful and many sites like this implement it. It was just an idea for you.

  • I had thought of this @bigown, it’s really quite interesting to look for proximity. Allows the user more search dynamicity, I will condense immovable and address in the same table and try to follow with neighborhood and city as entities.

  • 2

    @Marceloaymone even because they are really entities. Better register the new neighborhood when you need, than become a mess. A reminder: neighborhood is city entity, and not the general register. This facilitates even more the organization. Even if two neighborhoods have equal names in different cities (or the Center, for example), treating separately is conceptually better. " coincidence" should not be converted into rule ;)

  • It is worth taking a look here: http://answall.com/questions/622/qual-a-bestmanly-de-se-represent- um-addressees%C3%A7o

Show 7 more comments

1 answer

3


According to the comments and aspects presented in the question, the best way to model its tables is the following:

  • Immovable: id, nome, cidade_id, rua, numero, complemento, cep, bairro, ...
  • Cities: id, name, state, ...

The state placed in Cidades can be a table Estado or a enumerable within its application, if the application only works for Brazil.

We can join Enderecos and Imoveis within a single table, since the cardinality will always be 1 to 1 between these two tables, and maintaining two objects can cause unnecessary inconsistencies.

A neighborhood registry can be interesting if there are a few cities, but it becomes laborious considering that your system can work with the entire country. For example, to register a property, the neighborhood would have to exist first. Not only that, in cities like São Paulo, there is no agreement with the name of the neighborhood in certain regions. I’ve seen records where an address appears in up to 3 different neighborhoods.

Therefore, the best to Bairros is to use a column bairro, where the name of the district can be specified in full.

To Cidades, the best is Imoveis referencing it directly. You’ll get your city search easily this way. Going back to the problem of neighborhoods, the join for the research would be larger and more complex, besides having the aggravating problem of dissonance between neighborhoods of the same city, as explained above:

select c.*, b.*, i.*
from Imoveis i
inner join Bairros b on i.bairro_id = b.id
inner join Cidades c on b.cidade_id = c.id
where c.nome like "%expressao%";

In the form suggested in the answer, it is:

select c.*, i.*
from Imoveis i
inner join Cidades c on i.cidade_id = c.id
where c.nome like "%expressao%";

  • State has no need, the focus of the application is only on the state of santa Tarina, focusing on the region of the capital and adjacent.

  • Better yet. The field can be removed from the modeling, so.

  • 1

    whereas I will need to search for property registered in a particular neighborhood of a given city, and would like only cities and neighbourhoods with registered properties to appear on this list, allowing the neighborhood to register manually, how to avoid duplicity in cases of a badly typed neighborhood name? I was thinking of making a "Add neighborhood/city" button that could be saved via ajax a new neighborhood, and automatically reloaded in select for the user to include a neighborhood/city that does not yet exist in sgbd

  • 1

    The coolest way to do that would be to record the name of the phonetic neighborhood. For example, if the user writes "Cerqueira Cézar", "Cerqueira Cesar" or "Cerqueira César", the phonetic algorithm would be a single String to all three, avoiding these registration problems. In the survey, searching for any of the Strings example would return all results. Look at this question where this is dealt with in more detail.

  • 1

    I found it interesting, but a cannon to kill flies. It is only a real estate site with searches for cities, neighborhoods, rooms, etc.

  • It’s not as hard as it looks. See this other link.

  • 2

    Not for the difficulty, the business of the site is to sell real estate, not register with the maximum efficiency, even because, the registration will be by the property itself, significantly reducing the chance of error, I even thought of using a webservice as search cep, for being simpler, as to my doubt of normalizing or not, the cities and neighborhoods, would be more focused on facilitating the search through details like these.

  • 1

    Well, this is possibly scope for another question ;)

Show 3 more comments

Browser other questions tagged mysql sql relational-model

You are not signed in. Login or sign up in order to post.