Thanks, Toby. Glad it was of interest.

With regard to the language issue; of course you are right that I over-simplified. I was trying to come up with a schema that could show some relatively complex joins: I thought about the family, the CD collection and the farm, but they've all been done ;-)

Language is a thorny problem. As you say, some artists might speak a primary language that can not be derived from their country, so Language should probably be a property of the Artist. And some artworks might indeed be "in" a language that is not the artist's primary language ... so the Language should also be an attribute of the Artwork ... but does a Painting have a Language? Probably it needs to be nullable, or maybe an attribute of only certain types of artwork in their respective tables.

With regard to the primary key in the Painting, Play and Poem tables, and having an extra column named <entity_type>_id, as I mentioned I prefer to have separate names because it can make column aliasing simpler, and also because it allows for future expansion.

What if I realize that most poets publish collections of poetry as an artwork? I will have to add a name column to the Poem table and backfill it. But other than that, all I have to do is drop one unique key: 

unique key poem_artwork_uk (artwork_id)

[download]
... add a different multi-column unique key: 
unique key poem_name_artwork_uk (name, artwork_id)

[download]
... rerun dbicdump, and my classes will be rebuilt with a one-to-many relationship between Artwork and Poems (with all custom code preserved).

Then I could get the number of Poems in the collection, or the other Poems, with code like: 

my $poem = $db->resultset('Poem')->search(
    {
        'me.name'     => 'How Great It Is To Frobnicate',
        'artist.name' => 'Toby Inks',
    }, {
        join => { artwork => 'artist' },
    },
)->single;

my $num = $poem->artwork->poems->count;

my @other_names = map { $_->name } $poem->artwork->poems->search({
    poem_id => { '!=' => $poem->poem_id },
})->all;

[download]

Won't be tweaking this demo any more for now though :-)

Thanks,
--nick


The way forward always starts with a minimal test.

In reply to Re^2: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?) by 1nickt
in thread DB: what kind of relationship? by bliako

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.