For better performance in the Postgresql database is it better to have the products divided into smaller tables?

Asked

Viewed 52 times

-1

Hello,

I’m developing a system to manage the sale of several real estate developments. In this system each enterprise has a group of units available, type "Apartment 21 - 2nd Floor, Apartment 34 - 3rd Floor" etc. I need performance for the broker to access a unit, see its information and even change the sales situation of that unit. Ex: from available to sold. As the same construction has several projects and I serve with the same system several constructors; the system has registered in the same table the units of many projects having a field "empreendimento_id" as identifier of which undertaking it is part to be used with filter on the sales mirror. So far so good. I used to use a "mirror_de_sales" table with all units, but over time the amount of enterprises was growing, the table also and began to get slow to access and change information of a unit. So I started using a table for each undertaking. But with this I make it difficult to make global inquiries about overall sales performance and a broker sells more than one venture and thereby makes it difficult to get a general consultation of his sales as they are distributed in several tables. I have no problem creating Querys to search the information by crossing several tables and gathering for the query. But I would like to know if I am correct in using a table per enterprise, or should have left all units in the same indexed by the id of the enterprise?

c# database postgresql performance

  • The ideal is to represent your need in a MER to analyze, "having the products divided into smaller tables" does not make much sense... you have to maintain the normalization of the data... other important data is the history of the records, what time can be sold, then returned, resold, etc... simplifying, could consider as a normal stock control and generate movement by sales / purchases / etc...

  • "but over time the amount of ventures was growing, the table too and started to get slow to access and change information from a drive" that’s how many records? performance issues are solved with indexes, queries and server configuration.

  • Hello Rovann, in the case of real estate developments ( allotments or residential ), the quantity of products is always the same and the sale of a unit does not make it cease to exist in the stock, because it is not a product that is delivered to the buyer and for as long as the financing exists the unit continues of administration of the manufacturer. The only difference from a unit sold to an available one is the "situation" field that goes from "Released" to "Sold". And with that is added a record in the table of proposals with the funding opening data.

  • The unit continues to be shown in the independent sales mirror of the situation. This is a type of product that makes a difference both for the customer and for the broker to see the situation of all units independent of the situation. The sale movement does not change the size of the table, but rather the addition of new ventures for sale. If it is a allotment of 1,000 lots, it is 1000 new units.

  • In the same way that a car is unique, and a dealership has a stock of vehicles, a broker/builder also has a stock of real estate (cars are also unique [each has its chassis]). Also pay attention to the question of the enterprise that is financed so it is still property of the financial (that can be the construction), in this case the enterprise leaves the stock of the 'available' and goes to the stock of the 'financed', I would think of something on that path... and the amount asked was of records at the time the system started to slow down...

2 answers

0

I believe it is even better organized. In my view, you should create 4 tables: construction, development, block and apartment. The sold flag must be set to apartment. A Join among these tables you could bring the list of apartments sold, with your block, development and construction. But of course, for you to create the MER needs to be well understood the business rule! Always make the primary and foreign key number and in case N filter fields are used use indexes! In a second moment, you can think about tunning (by touching the environment variables of postgresql.conf)

  • Could you give an example to complement the answer?

  • Thanks Carlos, I thought about the cost benefit. With separate ventures increases the response time for the broker to consult the sales performance information but decreases the access and editing time of the unit at the time of the sale process. I think it’s more important to have performance when selling.

  • Speak my dear Fabricio! Thank you for the prestivity my dear!

0


In this case the identity of the tables is very important. Because when doing the relationships with the index will help in the performance.

Browser other questions tagged c# database postgresql performance

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