Doubt with relational modeling

Question

Doubt with relational modeling

Navigation

#1 by (3 votes)

4

I am setting up a real estate register, 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 search real estate in a registered city, how does this relationship work, is it valid to duplicate the key within Real Estate, creating a ciudad_id?

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

Second situation: Select the city and list ahax neighborhoods that have a property registry.

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

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

Note: I'm not asking for the 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 the other information of the property, consistently saving the names of neighborhoods and cities, avoiding the duplication of the cadastre of these.

mysql sql modelo-relacional

asked by anonymous 30.06.2014 / 17:20

1 answer

How to transform a vector list into R? How to save and return images with Web Api?

score 3 · Accepted Answer

According to comments and aspects exposed in the question, the best way to model your tables is as follows:

Properties : id, name, city_id, street, number, complement, zip, neighborhood, ...
Cities : id, name, status , ...

The status put in Cidades can be a Estado table or else an enumerable within your application, if the application works only for Brazil.

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

A neighborhood register can be interesting if there are 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 Sao Paulo, there is no agreement with the name of the neighborhood in certain regions. I have seen cadasters where an address appears in up to 3 different neighborhoods.

So the best thing to Bairros is to use a bairro column, where the neighborhood name can be specified in full.

For Cidades , Imoveis is best referenced directly. You will be able to easily search for your city in this way. Returning to the problem of the neighborhoods, the join for the research would be bigger and more complex, besides having the aggravating of the problem of dissonance between districts of the same city, as I 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%";

As suggested in the response, it is:

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