Calculate date based on row values and timestamp

Question

So i'm trying to create a library database and was wondering if something i'm trying to achieve is possible through the use of triggers or similar.

I have this table called "category" which contains the different categories that you can borrow, like books or dvds.

And depending on what product category you lend from, you have different times allowed, like books you can borrow for 3 weeks but dvds are only allowed 3 days.

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | int(11)       | NO   | PRI | NULL    | auto_increment |
| name         | varchar(100)  | NO   |     | NULL    |                |
| time_allowed | time          | NO   |     | NULL    |                |
| days_allowed | varchar(400)  | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+

And then i have the table "Borrowed" which looks like this

+---------------+-------------------+-------+-----+----------------------+-----------------------------+
| Field         | Type              | Null  | Key | Default              | Extra                       |
+---------------+-------------------+-------+-----+----------------------+-----------------------------+
| id            | int(11)           | NO    | PRI | None                 | auto_increment              |
| item_id       | int(11)           | NO    |     | NULL                 |                             |
| date_borrowed | timestamp         | NO    |     | CURRENT_TIMESTAMP    |                             |
| return_by     | datetime          | NO    |     | None                 |                             | 
| returned      | enum('No','Yes')  | NO    |     | No                   |                             |
| borrower_id   | int(11)           | NO    |     | None                 |                             |
| time_returned | timestamp         | Yes   |     | CURRENT_TIMESTAMP    | ON UPDATE CURRENT_TIMESTAMP |
+---------------+-------------------+-------+-----+----------------------+-----------------------------+

Now what i want to do is take the "date_borrowed" from "Borrowed" + "time_allowed" + "days_allowed" from the "Category" table to create the datetime value for "return_by" in the "Borrowed" table.

So if the "date_borrowed" is "2016-01-01 12:00:00" and a dvd is borrowed then the "return_by" should be "2016-01-04 12:00:00".

Is this possible to do through Triggers ?

Any and all feedback is much appreciated.

UPDATE:

CREATE TABLE `borrowed` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) DEFAULT NULL,
 `date_borrowed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `return_by` datetime NOT NULL,
 `returned` enum('No','Yes') CHARACTER SET utf8 NOT NULL DEFAULT 'No',
 `borrower_id` int(11) NOT NULL,
 `time_returned` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `borrower_id_idx` (`borrower_id`),
 KEY `item_id_idx` (`item_id`),
 CONSTRAINT `borrowed_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `borrower_id` FOREIGN KEY (`borrower_id`) REFERENCES `borrower` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

---

CREATE TABLE `category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) CHARACTER SET utf8 NOT NULL,
 `time_allowed` time NOT NULL,
 `days_allowed` varchar(400) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

---

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_name` varchar(150) CHARACTER SET utf8 NOT NULL,
 `item_uniqueid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT 'This can be a Serial Number or a ISBN number. But has to be unique for that item',
 `item_status` enum('Ok','Damaged','Broken') CHARACTER SET utf8 NOT NULL,
 `item_comment` longtext CHARACTER SET utf8,
 `item_retired` enum('No','Yes') CHARACTER SET utf8 NOT NULL DEFAULT 'No',
 `item_dop` date NOT NULL COMMENT 'item_dop = Item Date Of Purchaes',
 `item_category` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_category_idx` (`item_category`),
 CONSTRAINT `id_category` FOREIGN KEY (`item_category`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Answer 1

First you need to allow NULLs for the return_by column:

ALTER TABLE `borrowed`
    ALTER `return_by` DROP DEFAULT;
ALTER TABLE `borrowed`
    CHANGE COLUMN `return_by` `return_by` DATETIME NULL AFTER `date_borrowed`;

Then create a BEFORE INSERT trigger for the borrowed table:

DELIMITER //
CREATE TRIGGER `borrowed_before_insert` BEFORE INSERT ON `borrowed` FOR EACH ROW BEGIN
    set new.return_by = (
        select new.date_borrowed 
            + interval c.days_allowed day
            + interval time_to_sec(c.time_allowed) second
        from items i
        join category c on c.id = i.item_category
        where i.id = new.item_id
    );
END//
DELIMITER ;

SQLFiddle-Demo

If you ever update the date_borrowed column, you will also need an UPDATE trigger to ajust the return_by value.

Answer 2

Assuming that you time_allowed is in HOUR, your days_allowed is in number of days (an not a varchar for string) and the two tables are related by a column relation_key and key you could suing a select llike this

select TIMESTAMPADD(HOUR,  c.time_allowed, 
       TIMESTAMPADD( DAY ,c.days_allowed,b.date_borrowed) ) 
from Borrowed as b 
inner join catogory on b.relation_key  = c.key