From: Martijn Tonies
Date: August 22 2004 1:39pm
Subject: Re: Calculated fields - is there any way to do this?
List-Archive: http://lists.mysql.com/mysql/171328
Message-Id: <00e501c4884d$6c96d4a0$0a02a8c0@martijn>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit




> In the last episode (Aug 20), Joshua Beall said:
> > Is there any way in MySQL to have calculated fields?  E.g., a make
> > "expiration" equal to the "lastPaid" date, plus 1 year.  Whenever
> > lastPaid gets updated, expiration can be updated as well.
> >
> > Is this possible?  I know it could be implemented client side, but I
> > am wondering if it could be done directly in MySQL?
>
> Not until views (will be in 5.0) or triggers (targeted for 5.1) are
> implemented.

Actually, there's also a thing as "computed fields" - which
aren't stored in the database, but really are the result of an
expression, calculated on-the-fly everytime you access such
a value. These columns cannot be updated.

For example, in Firebird, you could write

CREATE TABLE employees
(
  FIRST_NAME varchar(...),
  MIDDLE_NAME varchar(...),
  LAST_NAME varchar(...),
/* for display */
  FULL_NAME COMPUTED BY ( LAST_NAME || ', ' || FIRST_NAME || ' ' ||
MIDDLE_NAME )
)

"full_name" cannot be updated or inserted and it's value is not
physically stored in the database.

For you case, "expiration" would be a bit of a business
rule (lastpaid + 1 yr).

However, MySQL doesn't support this. With triggers, you
can do something like this, but the result will be stored
inside the database - which is different from "true" calculated
fields.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com