CREATE TABLE `item` (

	id INT UNSIGNED NOT NULL AUTO_INCREMENT,

	-- non null character: the character wears the object

	-- non-null spot: the object is at the specified place (spot)

	-- non-null parent: the object is inside the parent object

	-- Only 1 fields out of the 3 must be non-null, others must be NULL

	id_character INT UNSIGNED NULL,

	id_spot INT UNSIGNED NULL,

	id_parent_item INT UNSIGNED NULL,

	-- The item name (in this user's language)

	label VARCHAR(200) NOT NULL,

	PRIMARY KEY (`id`),

	CONSTRAINT

		FOREIGN KEY (id_character) REFERENCES owncharacter (id)

			-- @todo Restrict because I would have to drop the inventory when deleting the character (I might do that with a CASCADE maybe?)

			ON UPDATE CASCADE ON DELETE RESTRICT,

	CONSTRAINT

		FOREIGN KEY (id_spot) REFERENCES spot (id)

			ON UPDATE CASCADE ON DELETE RESTRICT,

	CONSTRAINT

		FOREIGN KEY (id_parent_item) REFERENCES item (id)

			-- @todo I don't know how to behave when deleting the parent of an object

			ON UPDATE CASCADE ON DELETE RESTRICT

)

ENGINE = InnoDB

COLLATE = 'utf8mb4_general_ci'

;



CREATE TABLE `item_property` (

	id_item INT UNSIGNED NOT NULL,

	id_property SMALLINT UNSIGNED NOT NULL,

	uint INT UNSIGNED NULL DEFAULT NULL,

	shortstring VARCHAR(16) NULL DEFAULT NULL,

	PRIMARY KEY (`id_item`, `id_property`),

	CONSTRAINT

		FOREIGN KEY (id_item) REFERENCES item (id)

			ON UPDATE CASCADE ON DELETE CASCADE

)

ENGINE = InnoDB

COLLATE = 'utf8mb4_general_ci'

;