CODEWITHSUNDEEP
mysqlsql
user1628340
user1628340

Reputation: 941

MySQL Calculate Derived Attribute

I have a derived attribute Fine in a MySQL table, whose value is

(CurrentDate - DueDate) * 0.5

CurrentDate and DueDate are stored in the table in the Date format.

How can I specify this formula for Fine attribute in my table

CREATE TABLE IF NOT EXISTS Loan (
         loanID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         BorrowDate  DATE       DEFAULT NULL,
         ReturnDate         DATE       DEFAULT NULL,
         DueDate         DATE       DEFAULT NULL,
--- Fine... What do I write for Fine here-------
         userID    INT UNSIGNED  NOT NULL,           
         itemID    INT UNSIGNED  NOT NULL,      
         PRIMARY KEY  (loanID) ,
         FOREIGN KEY (userID) REFERENCES LibraryUser (userID),
         FOREIGN KEY (itemID) REFERENCES Items (itemID)
       );

Upvotes: 2

Views: 36496

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You can't have a computed column "as is" in mysql.

Three solutions :

First : use triggers

see for example column calculated from another column

Second : create a view (Fine will exist only in your view, not in your main table).

view can contain computed columns without any problem.

CREATE VIEW v_computedLoan AS
SELECT
loanID, 
BorrowDate, 
ReturnDate, 
CurrentDate,
--etc
(CurrentDate-DueDate)*0.5 as Fine
FROM Loan

Third : keep calculation out of your db. Use this in your queries only.

EDIT : if CurrentDate is really the same as CURRENT_DATE() function (and then should vary), solution with triggers won't work, and CurrentDate shouldn't be stored in any table (but you can still use it in a view).

Upvotes: 5

Related Questions