I am trying to design an in-game item shop, similar to that of games like Clash Royale, whereby a user can purchase the same item multiple times and then use an item. Once a user has used an item, it has expired. An example item; XP boost, which doubles the amount of XP a user can earn for 15 minutes.
From my understanding my current design is normalised to 3NF.
Are there any obvious pitfalls in my design below?
-- Contains items available for purchase.
CREATE TABLE `item` (
`id_item` VARCHAR(32) NOT NULL,
`title` VARCHAR(16) NOT NULL,
`description` VARCHAR(32) NOT NULL,
`price` INT UNSIGNED NOT NULL,
`datetime_created` DATETIME NOT NULL,
`max_quantity_allowed` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id_item`));
-- Contains the items owned by a user. User can only multiple of the same item.
CREATE TABLE `user_item` (
`id_user_item` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`id_user` INT UNSIGNED NOT NULL,
`id_item` VARCHAR(32) NOT NULL,
`datetime_purchased` DATETIME NOT NULL,
`datetime_activated` DATETIME NULL,
`datetime_expired` DATETIME NULL,
PRIMARY KEY (`id_user_item`),
FOREIGN KEY (`id_user`)
REFERENCES `user` (`id_user`)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (`id_item`)
REFERENCES `item` (`id_item`)
ON UPDATE CASCADE
ON DELETE CASCADE);