Hi Ken,

Your proposed structure for the joining table (many-to-many relationship) with 
additional attributes is exactly how I would approach it.  I have only worked 
with that model in CakePHP once, and it was many years ago. I wouldn't be 
surprised though if the framework does not provide a way to generate it via 
scaffold, and you need to set it up manually.  I think Cake builds the 
many-to-many model via belongsToMany(). There are some notes in the 
documentation on how to use the 'through' attribute to deal with extra 
conditions in the join table:
https://book.cakephp.org/3.0/en/orm/associations.html#using-the-through-option

I'm sorry I can't help too specifically with CakePHP, but the student <--> 
course | days_attended | grade model in the documentation example seems 
similar to your model. 

Good luck,

-- 
++++++++++++++++++++
Michael Berkowski
University of Minnesota Libraries
[log in to unmask]
612.626.6137
PGP Public Key: http://z.umn.edu/mjbpubkey
++++++++++++++++++++


On Wed, 31 May 2017, Ken Irwin said:

> Hi folks,
> 
> TL;DR: when you have a database table whose whole job is to contain pairs of keys connecting ID's of related tables, what if you want to say more about that relationship (e.g. a time-frame in which that relationship existed)? I'm particularly working in a Rails-like CakePHP context.
> 
> My particular case:
> 
> I'm working on a database project with a faculty member who's interested in certain women who were associated with particular convents once upon a time.
> 
> Our prototype database has tables for
> 
> Women
> -id
> -name
> -birth
> -death
> -etc
> 
> Convents
> -id
> -name
> -location
> -etc
> 
> Convents_Women
> -woman_id
> -convent_id
> 
> I'm working in CakePHP, which has a Rails-like db structure, and this is how it likes to handle relationships.
> 
> What I'm wondering is this: if there is other information about a relationship other than that it exists (e.g. the dates during which a person was at a convent; what their role was during that time, etc), is there a preferred / customary way of representing that? I had imagined that the convents_women table might look like:
> 
> -woman_id
> -convent_id
> -role
> -start_date
> -end_date
> 
> But the Cake/Rails scaffolding system doesn't seem to have a way of working with that. Or at least I haven't found the magic words to find a good explanation.
> 
> I could, I imagine, subsequently add table fields to convents_women; but I wonder where we'd want to add that information in a generic application scaffold, or would I have to build some cockamamie extra view?
> 
> I'd welcome any thoughts on the matter. I'd also be delighted to hear from other folks who are using CakePHP. I'm very new to it.
> 
> Thanks
> Ken