A Derived Field in an SQLite3 Database

Question

Good Evening everyone,

Today I would like to ask a question relating to derived fields (also known as calculation fields) in SQLite3.

Utilizing two values stored within my database "weight" and "distance" I know it is possible to utilize them to perform calculations to return the value I want utilizing a formula that requires both.

However I am wondering if there is a way to set a field, by SQLite coding, to automatically generate its own value, so once they have entered there weight and distance and the calculation is performed the attribute field for "calories Burned" will then be populated.

Alternatively I have read on several websites that derived fields, for a 3NF database, should not be stored within the database due to the 3NF requirements, if so I will have to keep performing the select calculation when ever I wish to retrieve the value.

Thanks

JHB92

Answer 1

You can achieve this goal using a VIEW:

 CREATE VIEW MyTablePlus (Weight, Distance, CaloriesBurned) AS
   SELECT Weight, Distance, ((Weight / Distance) * SomeNumber)
   FROM MyTable

Now, you can performs your SELECT statements on MyTablePlus and your INSERT and UPDATE statements on MyTable.