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:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 12:32 To: [email protected] 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? _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
