I have the following table that will store a file (image, PDF, etc.)
CREATE TABLE `tbl_file` (
`id` INT(10),
`size` FLOAT,
`name` VARCHAR(45),
`type` VARCHAR(16),
`content` BLOB,
`date_time` TIMESTAMP,
PRIMARY KEY (`id`)
)
There are many tables that will have files (tbl_order & tbl_payment) and I want to use only one table to store files (tbl_file). However, some tables may have multiple files for each row.
For example, there are multiple files for one order (invoice, PO, BL, contract) and there may be multiple files for each payment, etc.
So I made the following junction tables for each table that may have multiple files ( I didn't include the foreign key code).
CREATE TABLE `tbl_order_file` (
`order_id` INT(10),
`file_id` INT(10),
PRIMARY KEY (`order_id`, `file_id`),
)
and
CREATE TABLE `tbl_payment_file` (
`payment_id` INT(10),
`file_id` INT(10),
PRIMARY KEY (`payment_id`, `file_id`),
)
the problem here, is that one file can be related to both tables. Maybe it's an advantage rather than a problem but I would like to know if there is a better way to do this or to find a way to restrict the file so it's only referenced in one table.
I am using MySQL with innodb engine.