[Dbix-class] Combining link tables

Will Hawes info at whawes.co.uk
Tue Oct 31 11:31:33 GMT 2006


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 => 
self.column".

So, questions:

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 
desired behaviour?



More information about the DBIx-Class mailing list