[Dbix-class] many_to_many data access

[Frank Schwach]

On 12/07/12 10:41, Patrick Meidl wrote:
> On Thu, Jul 12 2012, Frank Schwach<fs5 at sanger.ac.uk>  wrote:
>
> nice to see a "colleague" from one of my former employers on this thread
> ;-)

:-) in that case: greetings from Hinxton to Vienna!

>> On 12/07/12 09:29, Patrick Meidl wrote:
>>> On Thu, Jul 12 2012, Robyn Jonahs<learn.catalyst at gmail.com>   wrote:
>>>
>>>> The cities, states and country relationships are all many_to_many so
>>>> once I have one, I can do the others.
>>> as I mentioned before, in terms of a clean database design, shouln't
>>> your relationships look like this: City belongs_to a State, which in
>>> turn belongs_to a Country. having city, state and country as independent
>>> many_to_many on the store violates the true relationships between these
>>> entities.
>> except if you are expecting international customers: in many countries
>> there are no "states", so a city really only "might_have" a state and
>> the city itself "belongs_to" a country, The (optional) state also
>> "belongs_to" a country adn there is no need to establish the link
>> between cities and states at all
> you are right of course, and now that I think about it again I remember
> that I implemented it like that myself in a previous project.
>
> nevertheless, I still think that the State and Country relations should
> at least be on the City, not the Store, as the OP indicated.

I agree that it should not be on the Store for sure. However, unless you 
can get the data for lnks between cities and provinces for all countries 
somehwere out there (maybe you can), I would definitely want to avoid 
having to maintain those links myself.
You would either have to pre-build those tables and place all cities in 
the world in the correct province of their country or you don't 
pre-build it and popualte as customers add their data but then you might 
have to deal with inconsistencies as some people might enter incorrect 
associations. Just think of the case where someone just moved into a new 
city at the border betwen provinces and they  are not yet sure which one 
the city actually belongs to.
There is much less scope for error if you only maintain the associations 
between provinces and countries.
Personally, unless this is really crucial data for you (e.g. if you must 
have reports grouped by provinces and therefore normalisation is 
important), I just have the state/province as a varchar field in an 
address table without any associations at all and treat this as 
free-form data.



>> not to mention the problem you might have in some countries where
>> cities can even belong to more than one province.
> I decided to ignore this special case so far :)
>
>      patrick
>


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.