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 dragonchild (Archbishop) on Nov 01, 2004 at 13:45 UTC
I know this isn't possible in Postgres, and I suspect it is incompatible with SQL in general. SQL has no problem with it, and neither does Postgres. However, the basic structure of most RDBMSes have issues with data that is versioned over time. I run into this all the time with data warehousing. The solution isn't an SQL solution - it's a database engine solution, but those aren't going to be easy to have done. The best solution that I've thought up is to modify the InnoDB engine to do three things: shift from versioning rows using a version number to using a date keep all old copies of a row around add a MySQL-specific SQL extension to allow queries to access old versions of rows I talked with Jeremy Zawodny back in June when I took his MySQL class and he said that, theoretically, it should work. But, he said that the issue was tuits and that it would come a lot faster if I had a few tuits to get it started. shrugs 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.	[reply]
Re^3: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 01, 2004 at 14:50 UTC
It isn't difficult to keep the versioning in the application level, and I believe that's the rigth thing to do. It isn't a database's job to version your data, only to store and retrieve it. It's extra work of course, but not much extra work. And you should use a real, transactioned RDMS of course to avoid inconsistencies ;) `ESC[78;89;13p ESC[110;121;13p`	[reply]
Re^4: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 15:34 UTC
It isn't difficult to keep the versioning in the application level, and I believe that's the rigth thing to do. There's right, in terms of architecture, and right, in terms of performance and maintainability. While data versioning, according to some architectural viewpoints, belongs in the application, it's still, imho, a data issue. Consider how CVS and other source control applications work - the versioning is done in the engine (database, in our case), not the client (application, in our case). Furthermore, there is only one place that actually knows when data is changed - the database. Not all changes are guaranteed to occur through the application. In fact, most databases support more than one application, often written by different teams. By putting versioning in the common place, you gain a lot more certainty in how versioning will occur. "Write Once, Use Everywhere." Plus, there's the issue of how do you pull the versioned data out. If you could put it in your SQL statement, you reduce your network and application-side processing overhead, potentially by up to 90% in a heavily-used system. Plus, the RDBMS is written in highly-optimized C. Your client, in this case, is in (un)optimized Perl. I know where I'd prefer the decisions to be made. Additionally, this is a problem you want to have solved forever, no matter what you do. I hate having to solve the same problem over and over in an application when it should be solved in some service I'm using, whether it's in the database, Apache, mod_perl, or something else. And you should use a real, transactioned RDMS of course to avoid inconsistencies ;) MySQL actually has stronger default transaction support than Oracle, Sybase, and DB2. What's your point? 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.	[reply]
Re^5: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 01, 2004 at 16:05 UTC
Re^6: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 16:29 UTC
Some notes below your chosen depth have not been shown here
Re^5: Conditional many to many relationships with Class::DBI by mpeppler (Vicar) on Nov 02, 2004 at 07:28 UTC
Re^6: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 02, 2004 at 14:01 UTC
Some notes below your chosen depth have not been shown here
Re^4: Conditional many to many relationships with Class::DBI by poqui (Deacon) on Nov 01, 2004 at 22:15 UTC
In a perfect world, I would agree with you. The application layer should be where all changes are handled and tracked, but too often that is not the case. At my company, the source system has been through several different applications, with data from each still being used by some users. Also, there are folks with direct access to the data (via sql) who perform updates directly without benefit of an application. That leaves me (and my teammates in the data warehouse) with 2 choices: rely on the change/update dates in the source system and just accept that there are changes we won't track, or use some kind of Changed Data Capture (CDC). We have survived for 3 years on the former, and are now trying to institute the latter. CDC can take several forms, but the best, in my opinion, is one that uses the database engine itself. The database will generally do logging of transactions, and scanning that transaction log for the tables you are interested in tracking is the best way to capture all changes. We have gone from full table scans of the last 3 days worth of changes, and still missing a good percentage of them, to capturing 100% of the changes we are interested in and loading 25% of the data we did before. Plus, we took the burden off the source system and put it on our newer servers.	[reply]