M15U has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks !!!

I'm searching for a tutorial or a detailled explanation about how can I make two tables in DBI perl to be connected via a third index table. For example I'm parsing a newspaper article and I collect information. I'd like to have a table in which I store my parsed article, another in which I collect my information from the article, and finally an index table in which I have the following relationship : for article 1 I have 1, 2...n information, for article two I have 1, 2...n where each number is the key of the row. The table will only have two columns.

I'm searching for two days now a piece of code on the web which looks like what I discribed earlier.

Thank you

More precisely I'm talking about relational tables and the way which this kind of data can be genrated from perl

Replies are listed 'Best First'.
Re: DBI relation between tables tutorial
by daxim (Curate) on Mar 11, 2013 at 15:25 UTC
    This is a beginner database question, go to the next library and get an introduction to RDBMS design/SQL book.

    You are describing a Junction table. 

    $ echo '
    create table articles (id integer not null, parsed text, primary k
+ey (id));
    create table information (id integer not null, something text, pri
+mary key (id));
    create table articles_information (articles_id integer not null, i
+nformation_id integer not null, primary key (articles_id, information
+_id), foreign key (articles_id) references articles (id), foreign key
+ (information_id) references information (id));
' | sqlite3 M15U.sqlite

    [download]
    This example is already complicated enough that you should not bother with raw SQL in DBI, but use a higher-level ORM. DBIx::Class sample code: 
    use DBIx::Class::Schema::Loader qw();
DBIx::Class::Schema::Loader->naming('preserve');
my $schema = DBIx::Class::Schema::Loader->connect('DBI:SQLite:db=M15U.
+sqlite');
my $foobar = $schema->resultset('Articles')->create({ id => 1, parsed 
+=> 'foo bar' });
$foobar->add_to_informations({ id => 1, something => 'quux' });
$foobar->add_to_informations({ id => 2, something => 'fnord' });

    [download]
    As long as the foreign keys are set up properly, the junction table is filled automatically: 
    $ echo .dump | sqlite3 M15U.sqlite | grep INSERT
INSERT INTO "articles" VALUES(1,'foo bar');
INSERT INTO "information" VALUES(1,'quux');
INSERT INTO "information" VALUES(2,'fnord');
INSERT INTO "articles_information" VALUES(1,1);
INSERT INTO "articles_information" VALUES(1,2);

    [download]
[reply]
[d/l]
[select]

      Thank you so much, this is exactly what I was looking for, and sorry for the fuzzy description

[reply]
Re: DBI relation between tables tutorial
by blue_cowdawg (Monsignor) on Mar 11, 2013 at 14:43 UTC
        I'm searching for two days now a piece of code on the web which looks like what I discribed earlier.

    Usually when I can't find an answer to something via a google search it is because I haven't defined the search parameters correctly. If I understand correctly what you are trying to do is either a view or a join (or both.)

    What you haven't defined in describing your problem is what database you are doing this against as different databases have different implementations to work with.

    As far as DBI there are copius tutorials around the web. The rest is all about SQL syntax

    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
[reply]

Back to Seekers of Perl Wisdom