[Dbix-class] arbitrary table names - best practice?

[Charles Alderman]

I know this may not help, but I've worked on a project in a very  
similar situation.  In the end, the best business solution was a  
complete re-write of the application.  In general, I've learned it's a  
bad idea to let the application modify the schema.

I worked a contract job for a company whose application created new  
columns in one table for every new custom field that the users wanted  
to define.  Each new column was named custom_field_XXXXX like you have  
table_name_XXXXX.  Every new user or small group of users had a new  
instance of the database (it was mysql, think oracle schemas also).   
After a while the company realized they needed to run reports off one  
instance, so every instance needed to reconcile the cf_XXX columns by  
a column description.  The whole reconciliation project was a major  
pain, and the company decided (realized?) there was no future in the  
application.  Thus, the major development effort was behind a rewrite.

DBIx::Class::Schema::Loader and SQL::Translator were extremely helpful  
as a stop gap measure to just make things work.  (The original  
application was PHP -- they were moving to .NET).  They didn't ask me,  
but I think the whole thing could have been easily redone with DBIC  
and Catalyst.  I think you're on the right track.

Good Luck,

Charles Alderman

----- Original Message -----
From: John Napiorkowski <jjn1056 at yahoo.com>
Sent: Mon, 12 May 2008 18:17:41 -0700 (PDT)
Re: Re: [Dbix-class] arbitrary table names - best practice?



>
> --- On Mon, 5/12/08, James Kiser <james.kiser at gmail.com> wrote:
>
>> From: James Kiser <james.kiser at gmail.com>
>> Subject: [Dbix-class] arbitrary table names - best practice?
>> To: dbix-class at lists.scsys.co.uk
>> Date: Monday, May 12, 2008, 8:51 PM
>> I have a database that contains a few thousand tables that
>> all share
>> the same structure.  They are all named as
>> [db].[table][unique
>> number].  The unique number is basically the primary key to
>> another
>> table which is used to define the 'relationship'.
>>
>> Obviously, I don't want to create a class for each
>> table and would
>> ideally like to have one class that will dynamically use
>> the
>> appropriate table based on the primary key (unique number)
>> that I
>> have.  The right answer is to redesign the schema and there
>> are plans
>> to do this.  However, I need a solution I can work with
>> until those
>> changes take place.
>>
>> I have a working solution based off of
>> DBIx::Class::Schema::Loader
>> using the constraint option.  Is using
>> DBIx::Class::Schema::Loader the
>> *best way* to handle this situation?  Has anyone else had
>> to implement
>> something similar?  Are there any other alternatives?
>>
>> Thanks in advance,
>> James
>>
>
> I'm not sure if there is a better way to do this, but I'd sure like   
> to see an article/blog/something about you using Perl and DBIC to   
> deal with a DB with a few thousand tables.  I wonder if   
> Rails/ActiveRecord could even come close to starting up under such   
> conditions :)
>
> Good Luck,
> John
>
>> _______________________________________________
>> List:
>> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>
>
>         
> ____________________________________________________________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.    
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:   
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>