0

I have a Rectangle entity in my database with the following attributes:

CREATE TABLE Rectangle (
  Id           INTEGER PRIMARY KEY NOT NULL,
  SideA        INTEGER NOT NULL,
  SideB        INTEGER NOT NULL,
  Area         INTEGER
);

In the moment I insert my data, Area can be not specified. Whenever I press a "Update Area" button in my Java Application the Area value will be updated.

Now, if SideA is edited while using the application, I would like Area to be edited consequently at the same time. How to make an attribute of an entity dependent from others and be updated in real time? Is it possible without using a pattern observer at Java level but using specific keywords in postgres / SQL?

EDIT: As I noticed most of answers are asking me the same question I'll add this small note.

The real database I'm managing is actually needing to calculate the mass of galaxy using some parameters from both the Galaxy table and other related tables. It needs to store the mass of thousands of galaxies and the application will display them in groups of 100. Since displaying the mass list is a frequent action, while updating one of those values is happening very seldom, I decided to calculate all values once and to store them in the database. I know it's a redundant information, but IMHO in this situation is worth it.

Robb1
  • 4,587
  • 6
  • 31
  • 60
  • https://stackoverflow.com/q/8250389/330315 –  Aug 22 '17 at 07:41
  • Possible duplicate of [Computed / calculated columns in PostgreSQL](https://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql) – Laposhasú Acsa Aug 22 '17 at 09:09

2 Answers2

1

Most database engines support the concept of "triggers". You tagged your question Postgres - here's the documentation for that platform.

Please do read up on triggers and their risks - it's very easy to write a trigger that will grind your database performance to a halt. You now also have duplication in your code - the same logic needs to fire when you press you button in the Java application, and when you update the SideA attribute. You could, of course, put your calculation logic into a stored procedure, and call that from both places.

Finally - are you sure you need to precompute this? Databases are really fast today, and you could for instance create a view which includes the calculation in real time.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
0

Since Area equals SideA*SideB you would store redundant information in your database by doing something like this (I don't know if it's possible).

To fix your problem and to not store this redundant data you could do the calculation when querying the database with something like this:

SELECT  
SideA,  
SideB,  
SideA + SideB AS Area
FROM Rectangle
Patrick Adler
  • 196
  • 2
  • 9
  • Thanks for your answer but I simplified the real issue :) the real "Area" value is something a little more complicate to calculate, and since it's thousands of entries in the db I would prefer to store it instead of calculating it every time I need it! Moreover `SideA` would change really rarely, but I would like to have a consistent database all time. – Robb1 Aug 22 '17 at 07:39
  • @Robb1: why do you think you need to store it? How exactly are you calculating the area that makes you think it's too "complicated" to be done "on-the-fly"? –  Aug 22 '17 at 07:40