http://qs321.pair.com?node_id=317422

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

Hello Dear Monks

I'm having some trouble setting up a has_many relationship between 2 entities - a page entity and a column entity. The relationship I'm tring to use in my code is 'a page has many columns'.

If I structure the tables in the following way, I'm facing no problems.

Table page
page_id page_title
1 Geography
2 History
Table column
column_id column_title page_id
1 Geog 1 1
2 Geog 2 1
3 Geog 3 1
4 Hist 1 2

I set up all the DBI modules, etc... and in Page.pm I add a line:

__PACKAGE__->has_many(cols => 'Column');

Now I can get all columns of a page with the following code:

$columns = Page->retrieve(1)->cols;

Now good database design, has taught me that when one has a 1:N relationship, one should construct a new table to hold that relationship.
So, the foll structure:
Table page
page_id page_title
1 Geography
2 History
Table page_column
page_id column_id
1 1
1 2
1 3
2 4
Table column
column_id column_title
1 Geog 1
2 Geog 2
3 Geog 3
4 Hist 1

So now how do I code the relationships in my Class::DBI modules which would enable me to get all the columns of a page in the same was as above.
i.e.

$columns = Page->retrieve(1)->cols;

Thankz in advance for all suggestions and help and compliments of the season to all of you!

update:
Changed the 'columns' method to 'cols' since Class::DBI already has a 'columns' method
- Thanks to PodMaster

Replies are listed 'Best First'.
Re: Class::DBI has_many
by kal (Hermit) on Dec 29, 2003 at 13:32 UTC

    Actually, I think your second step is wrong. You still have a one to many relation - page has many page_columns - but now a one to one relation between page_column and column. I'm sure that's wrong: 1:1 usually indicates the two tables are the same object, and you would amalgamate the tables.

    I don't think there is anything wrong with the original 1:N design - are you sure you're not confusing the rule about M:N relations? Then you would have a new entity table (as you have) and two one-to-many relations to the original tables.

    I know this isn't answering your question, and I always hate it when monks answer "Your question is wrong, here is the answer". But, I'm fairly sure you don't want the above design, and it not working in Class::DBI easily is a good indicator of that.

Re: Class::DBI has_many
by PodMaster (Abbot) on Dec 29, 2003 at 13:33 UTC
    See "Mapping" in "has_many" in the Class::DBI docs.
    update: also, i'm sure you know Class::DBI provides columns method...

    MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
    I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
    ** The third rule of perl club is a statement of fact: pod is sexy.


      HI,

      I read the mapping docs, and I just can't get it to work. An example pertaining to the above scenarion would be really helpful!
      Thanks
      --
      arc_of_descent

        I read the mapping docs, and I just can't get it to work. An example pertaining to the above scenarion would be really helpful!
        What did you try? I'm lazy, and I don't like to type.

        MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
        I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
        ** The third rule of perl club is a statement of fact: pod is sexy.

Re: Class::DBI has_many
by hardburn (Abbot) on Dec 29, 2003 at 14:38 UTC

    Now good database design, has taught me that when one has a 1:N relationship, one should construct a new table to hold that relationship.

    I think you're over-normalizing here. This is a good idea for many-to-many relationships (there is no end to the half-baked ideas for doing that without a join table), but a join table on a one-to-many relationship is overkill.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

Re: Class::DBI has_many
by boo_radley (Parson) on Dec 29, 2003 at 15:49 UTC

    Now good database design, has taught me that when one has a 1:N relationship, one should construct a new table to hold that relationship.
    This is more than third normal form requires, which is usually sufficient for most purposes. If you're striving for fourth normal form (or the lofty and mostly-academic fifth), your strategy might be appropriate.

Re: Class::DBI has_many
by CountZero (Bishop) on Dec 29, 2003 at 16:29 UTC
    Provided there is a 1:1 identity between Column_id and Column_title and a column can only be found on a single page (i.e. the same column will not be found on many pages), then there is no reason to split your original "table column" into "table column" and "table page_column".

    The only "1:N" relationship I see is the "one page has many columns" and that you need to normalize, which you did by making the original "table column". In that table there is no "1:N" relationship left to further normalize.

    CountZero

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

Re: Class::DBI has_many
by arc_of_descent (Hermit) on Dec 30, 2003 at 10:58 UTC

    Well.. I finally got it working the way that I wanted. And yes - a column may appear in more than 1 page.

    Here is the way I have designed it.
    Table page
    page_id page_title
    1 Geography
    2 History
    Table page_column
    column page
    1 1
    2 1
    3 1
    4 2
    Table column
    column_id column_title
    1 Geog 1
    2 Geog 2
    3 Geog 3
    4 Hist 1

    Two things I noticed which Class::DBI required -
    1) In table page_column, "column" should be the primary key and not "page"
    2) while coding the relationships, its better to use "page" and "column" column names instead of "page_id" and "column_id"

    Now the "mapping" stuff as suggested by PodMaster works like a charm, and I can get all columns in page easily.

    Thankx a lot for all the help.
    I know that this design may not be realistic but it very much suffices me for now.
    --
    arc_of_descent