[Dbix-class] many_to_many data access

[Robyn Jonahs]

On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt <frioux at gmail.com> wrote:

>
>
> On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl <patrick at pantheon.at>wrote:
>
>> first, to get the cities associated to the store, use the many_to_many
>> relationship you defined; if you have such a relationship, you usually
>> never use the bridging table (store_cities in your example) directly.
>>
>
> I disagree.  The use of data other than left_id and right_id is what makes
> many_to_many jointables so awesome.  The obvious data to put in the join
> table is the date the intermediate was created, or often which user added
> it.  Once I created a DB that was supposed to represent judges in the US.
> It had tables for Military Rank and Military Service and then a join table
> that joined the judge to those two and it had start dates and end dates
> etc.  That db had a total of 14~ join tables, nearly all of which had
> intermediate data.  It's a very handy and powerful pattern.
>
> --
> fREW Schmidt
> http://blog.afoolishmanifesto.com
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>

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.

The alternate if I insist on many_to_many tables is to have a Location
table that has records for unique combinations of those four things.

I was working under the concept that if any data entries were repeated in
database, you should put them into a separate table. 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.

I think that I have screwed up the database logic but I have confused
myself so much now that I could use help. Have I made it too complicated? I
guess a pitch back to the Best practice for dealing with addresses.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120712/fa3=
29a11/attachment-0001.htm