[Dbix-class] many_to_many data access

Robyn Jonahs learn.catalyst at gmail.com
Thu Jul 12 14:54:09 GMT 2012


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.
>

Wow, you guys are great. I was trying to build an app to help my real life
and at the same time learn Catalyst better. What I was trying to build was
a helper to track the prices of things I buy regularly. I used to keep a MS
Excel (Ack) file with a table of prices and the low price that I saw. This
was great but after keeping this for years all I had was the lowest price I
saw in N years which was getting on to be > 5. So I figured time to dust
off the little I learned about Catalyst and make a more powerful data
tracker. I also move around a lot so being able to keep things separate
seemed to make sense since prices of things I may buy vary with country,
state/province, city... (The main failure of my app is data entry which I
have to do when I look at prices of things each week.... it would be nice
to somehow get the prices automagically for the things I tend to shop for.)

In any case I track prices
among other data, each price is associated with a store and the store needs
a location to identify it. Instead of having just an address associated
with the store, I figured I would be reusing country, state/province and
city (proxy for address as I did not think I needed that much info). To
maximize reuse of the state/province, city and country tables, I thought
that many to many was best at the time I designed my Database. This I
figured allowed the most flexibility but I am doubting if that was the best
practice. I probably have not made it all best practice.

For the addresses, I have
Stores
 many_to_many with cities, states, and countries

I thought that way the many_to_many would let me have concise tables for
each of these and then the join tables would be able to keep it all
straight. This way, there would not be a lot of repeated data, e.g. London
would not be typed into a field each time but once and then the ids would
make the proper association.

I agree with the maintainability issue if it were open to others and they
added wrong data. I did not want to populate a table with all the cities in
the world and the countries and the state/provinces, I was letting that get
generated as time went on, it seemed simpler.

I have a select list for the store (hopefully with store city,
state/province country) in a select list and have a separate controller to
add a new city, state/province or country. This was my attempt to keep it
clean with respect to data entry. This is only my fourth or fifth Catalyst
application that I am playing with to learn so I trying to figure out the
best practice.

That is the goal, if that helps guide the excellent comments, I would love
to hear what works best for keeping track of addresses and letting them be
global. Global in the sense that I want to get all records on a city or a
country (that seems unlikely) or a province (which also seems unlikely). Am
I opening up a huge can of worms by asking for a best practice on tracking
addresses?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120712/dd5=
b1ac3/attachment.htm


More information about the DBIx-Class mailing list