Class::DBI has

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');
[download]

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

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

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;
[download]

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

--
arc_of_descent

Comment on Class::DBI has_many Select or Download Code

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.

[reply]

Re: Class::DBI has_many
by PodMaster (Abbot) on Dec 29, 2003 at 13:33 UTC

update:

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.

[reply]

Re: Re: Class::DBI has_many

by arc_of_descent (Hermit) on Dec 29, 2003 at 13:48 UTC

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

[reply]

Re: Re: Re: Class::DBI has_many

by PodMaster (Abbot) on Dec 29, 2003 at 13:57 UTC

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!

[reply]

Re: Re: Re: Re: Class::DBI has_many

by arc_of_descent (Hermit) on Dec 29, 2003 at 14:07 UTC

5Re: Class::DBI has_many

by jeffa (Bishop) on Dec 29, 2003 at 14:20 UTC

Some notes below your chosen depth have not been shown here

Re: Re: Re: Re: Re: Class::DBI has_many

by PodMaster (Abbot) on Dec 29, 2003 at 14:32 UTC

Re: Re: Re: Re: Class::DBI has_many

by boo_radley (Parson) on Dec 29, 2003 at 15:34 UTC

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

[reply]
[d/l]

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.

fourth normal form

[reply]

Re: Class::DBI has_many
by CountZero (Bishop) on Dec 29, 2003 at 16:29 UTC

and

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

[reply]

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

[reply]


Do you know where your variables are?
	PerlMonks