My database has a `file` table used to catalogue file meta info
(mimetype, filename, size, etc). Over time various other tables have
come to reference the file table (e.g. article, product,
product_category) and I now have several link tables: article_file,
product_file, product_category. I currently have things set up with DBIC
using has_many / many_to_many relationships in the normal way.
Since all these link tables have identical structure, with a column for
the article/product/product_category in question and a column for the
file object, I'm thinking it might make sense to merge them.
I'm thinking of using a table along the lines of
CREATE TABLE object_file(
`id` int(10) not null auto_increment primary key,
`table` char(50) not null,
`pk` int(10) not null,
`file` int(10) not null
where `table` would store e.g. "article", "product", "product_category",
`pk` would store the primary key value of the
article/product/product_category in question and `file` the primary key
value of the file.
I would need to pass the table name of the
article/product/product_category as well as its pk value when searching
for related files, but I don't see a way to specify a relationship
condition containing a fixed value, only in the form "foreign.column =>
1) Is this a really bad idea?
2) If not, can it be implemented with relationships, or would I need to
write custom accessors or use a custom resultset class to get the