[Dbix-class] many_to_many data access

[Patrick Meidl]

This is getting a bit OT, but anyway...

On Thu, Jul 12 2012, Robyn Jonahs <learn.catalyst at gmail.com> wrote:

> Conceptually I have worked myself into a confused state. Originally I
> thought that the many_to_many relationships were the proper way to go.
> But the problem is that the locations have to be unique. I did not
> generate that. My problem is that I am not making unique addresses. I
> think that I need a way to join the data record of interest (FOO) to a
> combination of store, city, state and country. So it seems that I need
> a join table between FOO_id and the set of (store_id, city_id,
> state_id, country_id).  This seems wrong to me.

from my understanding of your problem, your unique location is the
store, so your data record FOO only needs to be joined to a store. the
store, in turn, is in a city, state and country - or more than one, if
you want to make things complicated, but I don't think that's really
necessary for your problem.

> I was working under the concept that if any data entries were repeated
> in database, you should put them into a separate table.

yes, that's roughly what's called "normalisation".

> It may be that I need to use a simple has_one and has_many
> relationship to have a unique address record. I can't see how to make
> the many_to_many work at the moment in my mind.

you said in one of your posts that a particular store is in a single
city, so that's not a many_to_many, but a many_to_one (or a belongs_to
in DBIc terms). then, if you ignore the special cases discussed earlier
in this thread, you could model your data so that a city belongs_to a
state, and a state belongs_to a country.

there are two reasons why you want to model the relationship city ->
state -> country in such a rigid (and maybe simplified) way:

first, it helps you maintain data integrity. after all, you don't want
anybody to enter a store which is in Paris, Bavaria, UK, right?

second, if you are talking about an application with world-wide records,
not modelling a location in the described way would give you a hard time
designing the UI. I tend to implement the location selection as a
multi-step hierarchical operation, where you first select a country from
a pulldown, then the state from a pulldown which is populated with the
values for this country, then the city (all this done with ajax), and if
any of these entities doesn't exist, the user can enter a new one. if
you don't model your data hierarchically in the database, you couldn't
do that.

one thing I would recommend is to fully populate at least the country
table in the database. this can be easily done with code like this:

--8<-------------------------------------------------------------------

use Locales;

my $country_rs = $my_schema->resultset('Country');
my $en = Locales->new('en_gb');

foreach my $code ($en->get_territory_codes) {

    my $name_en = $en->get_territory_from_code($code);
    $code = uc($code);

    $country_rs->create(
        {
            iso => $code,
            name_en => $name_en,
        }
    );
}

--8<-------------------------------------------------------------------

anyway, all this is just my 2p, I'm not claiming this is best practice,
and am happy to be corrected :)

cheers

    patrick

-- 
Patrick Meidl ........................ patrick at pantheon.at
Vienna, Austria ...................... http://gplus.to/pmeidl