CODEWITHSUNDEEP
sqldatabasepostgresql
Robb1
Robb1

Reputation: 5025

How to create a dependency between attributes in an entity of a database?

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.

Upvotes: 0

Views: 108

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29619

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.

Upvotes: 1

Patrick Adler
Patrick Adler

Reputation: 196

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

Upvotes: 0

Related Questions