Re: Conditional many to many relationships with Class::DBI

I suspect this started as a standard XREF table between User and Group. Then, you had the new requirement that users can change groups and that change needs to be tracked.

There are a few ways to solve the problem. The first is to maintain a history table that tracks all the changes and your main table only has the most current data. This, however, doesn't really allow you to do reports backwards in time. No good.

The second solution is to change your thinking. This isn't a many-to-many relationship - it's a many-to-many-to-many relationship. Your primary key has shifted from (User, Group) to (User, Group, LegalDateRange), and you need to change your thinking based on that shift.

I don't know how to solve the CDBI issue as I've never really used it. However, that is the root cause of the problem you're having.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Comment on Re: Conditional many to many relationships with Class::DBI

Replies are listed 'Best First'.
Re^2: Conditional many to many relationships with Class::DBI by CountZero (Bishop) on Nov 01, 2004 at 19:41 UTC
The problem with Class:DBI is that it only supports single field keys and cannot work with multiple field keys. You would have to further normalize your tables so they all have single field keys to get it to work. I don't think it is worth the bother and it is better to add in your class some custom-written sql (`__PACKAGE__->set_sql(your sql here)`) which you can access by using the search_your_sql statement. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: Conditional many to many relationships with Class::DBI
by CountZero (Bishop) on Nov 01, 2004 at 19:41 UTC

Class:DBI

__PACKAGE__->set_sql(your sql here)

your_sql

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]