Create a Table to Store Files in the Database

[mdeslippe]

Overview

• Create a migration that adds a files table to the database (make sure it supports Unicode).
• Enable table compression on the new table.
• When users are deleted, automatically delete all files that belong to the user.
• Update any necessary documentation.

File Information that Should be Stored in the Database

• id - An identifier that uniquely identifies the file.
• user_id - The identifier of the user that the file belongs to.
• file_created_at - A timestamp of when the file was created.
• mime_type - The file's MIME type.
• name - The name of the file, which includes the file extention.
• data - The data contained in the file.

Information Type	Field	Data Type	Max Size	Primary Key	Foreign Key	Unique	Nullable	Default Value
id	bigint	N/A	✔️	❌	✔️	❌	auto_increment
user_id	bigint	N/A	❌	✔️	❌	❌	N/A
file_created_at	timestamp	N/A	❌	❌	❌	❌	Current Date & Time (UTC)
mime_type	varchar	1024	❌	❌	❌	❌	N/A
name	varchar	1024	❌	❌	❌	❌	N/A
data	longblob	N/A	❌	❌	❌	❌	N/A

SQL

CREATE TABLE `files` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT(20) UNSIGNED NOT NULL,
    `file_created_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `mime_type` VARCHAR(1024) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `name` VARCHAR(1024) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `data` LONGBLOB NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK__users` (`user_id`) USING BTREE,
    CONSTRAINT `FK__users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
AUTO_INCREMENT=1;