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