[Dbix-class] Combining link tables

[Hermida, Leandro]

Hello,

> Is this a really bad idea?

In general I think yes it is a bad idea because you are starting to use
an EAV kind of approach instead of a good relational approach which in
general is not good.  I don't see why you shouldn't just leave the
many-to-many tables the way they are (proper relational design).  Let me
make sure I am deciphering what you said your schema is like... tell me
if this is correct (enough - don't worry about my datatypes):

CREATE TABLE file (
  id         int(10)      not null auto_increment primary key,
  mimetype   varchar(50)  not null,
  name       varchar(200) not null,
  size       int(10)      not null,
  ...
);

CREATE TABLE article (
  id         int(10)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ...
);

CREATE TABLE product (
  id         int(10)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ...
);

CREATE TABLE category (
  id         int(10)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ...
);

CREATE TABLE article_file (
  article_id   int(10) not null,
  file_id      int(10) not null,
  CONSTRAINT PRIMARY KEY (article_id, file_id)
);

CREATE TABLE product_file (
  product_id   int(10) not null,
  file_id      int(10) not null,
  CONSTRAINT PRIMARY KEY (product_id, file_id)
);

CREATE TABLE product_category (
  product_id   int(10) not null,
  category_id  int(10) not null,
  CONSTRAINT PRIMARY KEY (product_id, category_id)
);



-----Original Message-----
From: Will Hawes [mailto:info at whawes.co.uk] 
Sent: Tuesday, October 31, 2006 12:32
To: dbix-class at lists.rawmode.org
Subject: [Dbix-class] Combining link tables

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?