SQL Relational tables theory

[Craig White]

On Mon, 2006-01-30 at 23:59 -0700, Derek Neighbors wrote:
> Craig,
> 
> Craig White wrote:
> > I have a number of tables but specifically...
> >
> > clients
> > placements
> > facilities
> >
> > A client may have only 1 placement
> > A placement may have only 1 client but there may be many placements per
> > client.
> >   
> I'm confused. 
> A client may have only 1 placement. 
>  AND
> There may be many placements per client.
> 
> Appear to be opposing statements.  Which is it?  Can a client have one 
> placement or many placements?  or is it too late and I am misreading this?
> > A placement may have only 1 facility but there may be many placements
> > per facility
> > A facility may have only 1 placement but there may be many placements
> > per facility
> >   
> Again confusion.  How can a they only have 1 placement, but maybe many?
> > I'm wondering if it is necessary to store a facility relation in the
> > client table since the client will have one relation to the placement
> > and the placement will have a one relation to the facility.
> >
> > I do anticipate needing frequent reports that list clients per facility
> > so that would be an advantage to store the facility in the client.
> > Should I be creating a relationship between client and facility when the
> > real relationship is between client and placement and the other real
> > relationship is between placement and facility?
> >   
> The natural language used makes this confusing, but if you really need 
> the many relationships you need to create additional tables...
> 
> clients
> placements
> facilities
> client_placements
> facility_placements
> 
> client_placements
> -----
> id
> client_id
> placement_id
> 
> facility_placements
> ----
> id
> facility_id
> placement_id
> 
> Again... it's past my bedtime so maybe I misread this.  I noticed you 
> posted on the rails list.  Know that ActiveRecord has some magic mojo to 
> make these relationships VERY transparent. :)
----
I did post on rails list but I was thinking in general SQL setup and I
have been working through the magic mojo. My other thinking is that the
db modeling is important to get right.

Let me clarify a bit since my natural language (not putting in specific
PostgreSQL or ruby terminology) is probably as unclear as my thinking
generally is...

I create a client and a placement virtually simultaneously since a
client must be 'placed' into the system. The client may be discharged -
thus his placement would have an exit date. He may come back into the
system and thus have another placement but I intend to retain his
earlier placement record but his client record would be linked to his
new placement. I am thinking this is the methodology since I would want
to keep track of each client placement into each facility. 

I have used db's like Filemaker which I have gained a lot of experience
with the usage and limitation but not so with any SQL db/tables.

My question stems from not knowing the impact of frequent
listings/reports/etc. that would have clients by facility when my
relationships would be...

Clients < - > Placements < - > Facilities

I am wondering if I should have an additional relationship and make it
like this...

Clients < - > Placements < - > Facilities
  |                                |
  |----<------------------------->-|

Which might be workable in rails voodoo or might require some extra mojo
to make voodoo but I am trying to achieve a good relational db model
which I have to believe transcends the voodoo of the moment (rails)
since it is possible/likely that the client tools will evolve.

Thanks

Craig